Array formula to count unique items

[rubyb]

Hello all,

I am adapting an array formula that I read about from this link (great resource by the way). The purpose is to count the number of unique items in a column based on specifed conditions, without using conditional formatting, filters, etc.

The range where I am setting the criteria is named dynamicCountry (or, $D$2:$D$596) and the range where the unique items are to be counted from is named dynamicData (or, $F$2:$F$596).

The one "twist" is that that although I am searching based on one single criteria, the criteria is spelled differently throughout the range. For example, if I am searching for "Canada", but it is sometimes presented as "Canada"or "Can.".

I have tried to rearrange the formula to account for these alternatives, but each way I have tried returns either 0 or #DIV/0.

I have tried with wildcards and using nested "AND" statements, such as:

{=SUM(IF(AND(dynamicCountry="Canada",dynamicCountry="Can.",1/(COUNTIFS(dynamicCountry,"Canada",dynamicCountry,"Can.",dynamicData,dynamicData))),0))}

{=SUM(IF(dynamicCountry="*can*",1/(COUNTIFS(dynamicCountry,"*can*",dynamicData,dynamicData)),0))}

Any ideas or suggestions would be much appreciated!

 

[Aladin Akyurek]

Perhaps the following would help:

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(dynamicData<>"",
  IF(ISNUMBER(SEARCH("can",dynamicCountry)),
  MATCH("~"&dynamicData,dynamicData&"",0))),
  ROW(dynamicData)-ROW(INDEX(dynamicData,1,1))+1),1))

 

[rubyb]

Thank you for your quick reply. I will try out your suggestion now and get back to you.

 

[rubyb]

Aladin you truly are magical! Your formula did just the trick. Many thanks, I had been trying to figure out this one for ages!

 

[Aladin Akyurek]

Aladin you truly are magical! Your formula did just the trick. Many thanks,

You are welcome.

I had been trying to figure out this one for ages!

...while we are around the block!