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!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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,141,575
Messages
5,707,176
Members
421,495
Latest member
jono_oh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top