Count unique values based on criteria in another column

2
votes

I m trying to count the unique values in column A based on criteria - "Blue" of column B. the answer should be 3 but i get 1. the answer should be an excel formula, no array formula or vba.

Formula:

=SUM(IFERROR(1/COUNTIFS(A1:A12,A1:A12, B1:B12, "Blue"),0))

Data:

A   Blue
B   Red
C   Green
B   Blue
B   Red
C   Green
B   Blue
B   Red
C   Green
C   Blue
B   Red
C   Green
1
excelexcel-formula
Hi, add your table as data in the question, as well as your formula. I'm looking at this now for you – Badja

1 Answers

0
votes

So =COUNTIFS(A1:A12,A1:A12, B1:B12, "Blue") is doing the following

  • Look in: A1:A12

  • For value (array): A1:A12

  • Also look in: A1:A12

  • For value: "Blue"

  • Then count

You'll only ever get 1 here because the lookup of A1:A12 will always be A1:12 - AKA 1 = 1

Where anything "Unique" is requested, an Array formula is almost always needed