MrExcel Publishing
Your One Stop for Excel Tips & Solutions

counting unique values


Posted by steven on December 28, 2001 4:06 AM

Hello,

I have a range of numbers, say from a1 to a100. I would like to have a formula wich counts the unique numbers in this range

ex.
1002
1005
1008
1002
1005

number of unique numbers = 3

thanks, steven

Posted by Aladýn Akyurek on December 28, 2001 4:33 AM

Steven --

The FREQUENCY formula that follows will give you the desired count:

=SUM(IF(FREQUENCY(A1:A10,A1:A10)>0,1))

where A1:A10 houses the numbers of ýnterest.

Aladýn

============

Posted by BobJ on December 28, 2001 8:51 AM

How about Text & Numbers?

U17R
U18R
U18R
U18R
U20R

Tx..Bj

Posted by Aladin Akyurek on December 29, 2001 7:10 AM

Bj --

It's

=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1:A10,A1:A10,0))>0,1))

Aladin