# Array formula to count unique items

#### rubyb

##### New Member
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(dynamicCountry="*can*",1/(COUNTIFS(dynamicCountry,"*can*",dynamicData,dynamicData)),0))}

Any ideas or suggestions would be much appreciated!

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

##### MrExcel MVP
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
Thank you for your quick reply. I will try out your suggestion now and get back to you.

#### rubyb

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

##### MrExcel MVP
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!

Replies
22
Views
307
Replies
13
Views
396
Replies
15
Views
135
Replies
6
Views
53
Replies
4
Views
65

1,109,405
Messages
5,528,573
Members
409,827
Latest member
Tmcgrew05

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...