Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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


Check out our Excel Resources

Re: counting unique values

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

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


Re: counting unique values

Posted by BobJ on December 28, 2001 8:51 AM
How about Text & Numbers?

U17R
U18R
U18R
U18R
U20R

Tx..Bj


Re: counting unique values

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.