carl wrote:
I have my data like so:

ColA ColB
1 A
1 B
2 A
2 B
1 C
1 D

Is there a way yo count the number of unique values in ColB if ColA value
equals 1.

So in the above, the answer would be 4.

Thank you in advance.


If you have Longre's morefunc.xll add-in:

=COUNTDIFF(IF($A$2:$A$7=1,$B$2:$B$7,0),FALSE,0)

which you need to confirm with control+shift+enter.

Or:

=COUNT(1/FREQUENCY(IF((A2:A7=1)*(B2:B7<""),MATCH(B2:B7,B2: B7,0)),ROW(INDEX(B2:B7,0,0))-ROW(B2)+1))

which must be confirmed with control+shift+enter.
--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.