CODEWITHSUNDEEP
excelexcel-formula
Error 1004
Error 1004

Reputation: 8230

Count unique values based on criteria in another column

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

Upvotes: 2

Views: 1177

Answers (1)

Badja
Badja

Reputation: 875

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

Upvotes: 0

Related Questions