The option (b) can be shorter:

=SUMPRODUCT(--(A2:A15="Include"),--(B2:B15<""),--((MATCH(A2:A15&"#"&B2:B15,A2:A15&"#"&B2:B15,0)=ROW (A2:A15)-ROW(A2)+1)))

I wonder if

=SUMPRODUCT(--(A2:A15="Include"),--(B2:B15<""),--ISNUMBER(MATCH(MATCH(A2:A15&"#"&B2:B15,A2:A15&"#"& B2:B15,0),ROW(A2:A15)-ROW(A2)+1,0)))

would calculate faster.

Aladin Akyurek wrote:
Two options...

{a) If you have the latest version of Longre's morefunc.xll add-in:

=COUNTDIFF(IF(A2:A15="Include",IF(B2:B15<"",B2:B1 5)),FALSE,FALSE)

which must be confirmed with control+shift+enter, not just with enter.

(b) With built-in functions:

=SUMPRODUCT(--(A2:A15="Include"),--(B2:B15<""),--((MATCH(A2:A15&"#"&B2:B15,A2:A15&"#"&B2:B15,0)=ROW (INDEX(A2:A15,0,0))-ROW(A2)+1)))


K wrote:

Thanks so much for this string, it was exactly the info I needed...
But - I tried this with my data (4000 rows, one column with values
"Include" and "Exclude" that is the criteria for counting the unique
text values in the second column), and ended up with a formula result
1043.4907. Do I round up or down???

=SUMPRODUCT((A2:A3964="Include)/COUNTIF(D2:D3964, D2:D3964&""))

I don't understand how the formula works or I would troubleshoot it
myself
"Bob Phillips" wrote:


=SUMPRODUCT((A1:A20=1)/COUNTIF(B1:B20,B1:B20&""))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"carl" wrote in message
...

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.






--

[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.