Count distinct values in excel with criteria on another column

Question

I need a formula to count distinct IDs from a table, based on Point Of Sale name with a wildcard, eg I need to find all distinct IDs from the table below, titles of POS of which contain word "China*". The result must be 4 (3333,4444,5555,1010).

Thanks for your help.

Answer 1

It's widely known that Frequency is much quicker. So if your IDs are numeric, you can use

=SUM(--(FREQUENCY(IF(ISNUMBER(SEARCH("China",A2:A10)),B2:B10),B2:B10)>0))

which must be entered as an array formula using CtrlShiftEnter

If the IDs are non-numeric you have to use:

=SUM(--(FREQUENCY(IF(ISNUMBER(SEARCH("China",A2:A10)),MATCH(B2:B10,B2:B10,0)),ROW(B2:B10)-ROW(B1))>0))

or similar.

See support article