find unique values in a range that contains text and empty cells

Hi,

I am trying to count the number of unique values in column B, given a certain criteria in column C. Both ranges are text. Column B may have empty values.

I have tried the following formula but cannot find a way to add an IF for the criteria (column C):

=SUM(IF(FREQUENCY(IF(LEN(B2:B100)>0,MATCH(B2:B100,B2:B100,0),""),IF(LEN(B2:B100)>0,MATCH(B2:B100,B2:B100,0),""))>0,1))

Have also tried the below formula, but doesn't work with empty cells in Column B:

=SUM(IF(FREQUENCY(IF(C1:C100="A",MATCH(B1:B100,B1:B100,0)),ROW(B1:B100)-ROW(B1)+1),1)).

Help please?

Thanks!!

Nic

Add &C2:C10000 to your formula:

Formula:

Please Login or Register  to view this content.

hi thanks for the prompt, however, I only need to count the number of unique values in B, if for example, value in C = A....

Maybe this (In D1 enter letter you searching for i.e. A):

Formula:

Please Login or Register  to view this content.

Migt be slow so keep ranges to minimum (i.e. 100 instead of 10000)

yeap that works a treat thanks very much!