Array formula to count unique items

rubyb

New Member
Joined
Mar 26, 2014
Messages
18
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,119
Perhaps the following would help:

Control+shift+enter, not just enter:
Rich (BB code):
=SUM(IF(FREQUENCY(IF(dynamicData<>"",
  IF(ISNUMBER(SEARCH("can",dynamicCountry)),
  MATCH("~"&dynamicData,dynamicData&"",0))),
  ROW(dynamicData)-ROW(INDEX(dynamicData,1,1))+1),1))
 

rubyb

New Member
Joined
Mar 26, 2014
Messages
18
Thank you for your quick reply. I will try out your suggestion now and get back to you.
 

rubyb

New Member
Joined
Mar 26, 2014
Messages
18
Aladin you truly are magical! Your formula did just the trick. Many thanks, I had been trying to figure out this one for ages!
 

Forum statistics

Threads
1,078,137
Messages
5,338,458
Members
399,234
Latest member
WaddoAU

Some videos you may like

This Week's Hot Topics

Top