Count the number of unique records based on multiple column criteria including partial string match

[Anglais428v2]

I have a dataset whereby I would like to count the number of unique records. I am doing this with the formula =SUM(--(FREQUENCY(IF(List=A2,ID),ID)>0))
Where List is a range, A2 is a piece of text (which is looked up against the List range) and where the IDs (record IDs) are unique. This works fine when A2 is matched exactly in the list. What I also want to do is include a count for the partial match.

E.g. if cell A2 is "United Kingdom", the formula counts all "united kingdom" in the list and subtracts any where the IDs are the same (i.e. deduping).
I would like to modify the formula so that it would count in the same manner but if cell A2 contained "united kingdom; united states". It should be able to get the partial match of "united kingdom" from "united kingdom; united states" and dedupe again based on the IDs.

Any ideas would be appreciated. If feel it is the highlighted part of the formula that needs fixing: =SUM(--(FREQUENCY(IF(List=A2,ID),ID)>0)) to incorporate the partial match, rather than full cell match.

 

[jasonb75]

There are a few ways you could do it, starting off with the simple methods, the first 2 that come to mind.

=SUM(--(FREQUENCY(IF(ISNUMBER(SEARCH(A2,list)),ID),ID)>0))

=COUNT(UNIQUE(FILTER(ID,ISNUMBER(SEARCH(A2,List))))

Note that you could get problems with partial search terms in the list, for example 'apple' in A2 would also count rows with 'pineapple' in the list range. Excluding such entries if it could be a potential problem with your data would need some not so simple methods.

 

[Anglais428v2]

Great, thanks Jason. I didn't think of incorporating SEARCH. This is working a treat. And noted regarding your second point.

 

[jasonb75]

You're welcome, thanks for the feedback

There are ways around the point that I mentioned, but I would only use it if you think it will be needed. If the list covers a large range then it could impact calculation time quite significantly.

Using your example of "united kingdom; united states" to set the format of the formula, you would need to change the search part to

ISNUMBER(SEARCH("; "&A2&";","; "&list&";"))

As with many things though, consistent entry is required for it to work.
For example, if you use a comma instead of a semicolon, or miss the space after the semicolon then it will not work correctly.