Unique count of a column based on text in another column

[srkreignz]

Hi,

What I have 2 columns
Column A Column B
John India, China
Jeffy US
Justine US, India
John China
John India

I would like to get the unique count of column A, for say India which will be entered by the user. So it should be case sensitive.

I tried the array formula

=SUM(IF(FREQUENCY(IF(B2:B10="*"&C2&"*",A2:A10),A2:A10)>0,1))

So that it searches string inside a text. This doesnt work this way..Please help.

Also tried the array formula

=SUM(IF(FREQUENCY(IF(Sheet1!B2:B10="*"&C2&"*",IF(Sheet1!A2:A10<>"",MATCH(Sheet1!A2:A10,Sheet1!A2:A10,0))),ROW(Sheet1!A2:A10)-ROW(Sheet1!A2)+1),1))

Doesnt work thos as well.
Please help

 

[Andrew Poulsom]

You can't use wildcards in a test for equality. Try:

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(C2,B2:B10)),IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0))),ROW(A2:A10)-ROW(A2)+1),1))

confirmed with Ctrl+Shift+Enter.

 

[srkreignz]

You can't use wildcards in a test for equality. Try:

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(C2,B2:B10)),IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0))),ROW(A2:A10)-ROW(A2)+1),1))

confirmed with Ctrl+Shift+Enter.

Thanks a tonne It worked well