# 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

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).

##### 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
3
Views
127
Replies
19
Views
457
Replies
29
Views
341
Replies
2
Views
225
Replies
5
Views
339

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.

### Which adblocker are you using?

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

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