Count of Unique Numbers in a range

[bradjedis]

Greetings!

I have the situation where I need to have a count of unique numbers in a range.

need to exclude any duplicates from the count.

The data is in column B.

1000111
1000212
1000111
1000212
1000045

etc

Thanks,
Brad

[HansV]

If there are no blank cells in between, you can use an array formula like this (confirm with Ctrl+Shift+Enter, and adjust the range B1:B5 as needed):

=SUM(1/COUNTIF(B1:B5,B1:B5))

[bradjedis]

Will do....

What if there are alpha characters? 3 letters at the beginning?

Thanks,
Brad

[HansV]

The formula will work for numbers and for text values.
Or do you mean that that you want to count the number of unique 3 letter start sequences in a series of values like

ABC123
ABC456
DEF789
GHI012
GHI345

[bradjedis]

sorry for the delay.... well, when I placed the formula, i get a division error. I was thinking it was because of the text I have in the fields.

Brad

[HansV]

Do you have blank cells in the range? That would cause the formula to return #DIV/0.
To exclude blank cells, use the following, still as an array formula (confirm with Ctrl+Shift+Enter):

=SUM(IF(LEN(B1:B5),1/COUNTIF(B1:B5,B1:B5)))

[bradjedis]

well, I could not get the above to work, however I did find an example that does do what I am looking for. It was on the Microsoft site.

http://office.microsoft.com/en-us/excel ... 81033.aspx" onclick="window.open(this.href);return false;

Had to cut/paste fix the range, then press f2, then ctrl/shift/enter

=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))

thanks!
Brad

[HansV]

I have attached a small sample workbook. Range A2:A10 contains a mixture of numbers, text, dates and logical values. The formula I proposed and the formula you got from Microsoft return the same count of unique values. I wonder why one works for you and the other doesn't...

CountUnique.xls

[Rudi]

Just my 2 cents:

1. A formula to count unique values: =SUM(IF(FREQUENCY(A1:A10,A1:A10)>0,1))
2. A formula to count unique strings: {=SUM(1/COUNTIF($A$1:$A$10,$A$1:$A$10))}

Please note: 2. is an array formula and must be entered using CTRL+SHIFT+ENTER

[HansV]

Rudi, your second formula is the same as the one from my original reply (with a different range). Brad mentioned that it doesn't do what he wants...

[Rudi]

I understand. My intentions were to show the difference between two formulas to do the task that Brad was requesting. One that only works with numbers, and one that is more flexible to count any entry in a range.

PS: I like choices and its in my nature to compare and analyse. :)