Count number of Unique values
 

A belated Happy New Year to All

Wondering if it is possible to count the number of unique values in a column
???

A column contains 5 digit numeric values and I am looking for a method that
will tell me how many different values are in the column.

ie 23456 34567 56789 23456 56789 = 3 ??

Any help appreciated

Thanks

Try something like

=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))

reference:
http://www.pcmag.com/article2/0,1759,1537551,00.asp


"Alan" wrote in message
...
A belated Happy New Year to All

Wondering if it is possible to count the number of unique values in a
column
???

A column contains 5 digit numeric values and I am looking for a method
that
will tell me how many different values are in the column.

ie 23456 34567 56789 23456 56789 = 3 ??

Any help appreciated

Thanks

Hi Alan

use the following ARRAY formula (enter with control & shift & enter, not
just enter)

=SUM(1/COUNTIF(A1:A10,A1:A10))

Cheers
JulieD


"Alan" wrote in message
...
A belated Happy New Year to All

Wondering if it is possible to count the number of unique values in a
column
???

A column contains 5 digit numeric values and I am looking for a method
that
will tell me how many different values are in the column.

ie 23456 34567 56789 23456 56789 = 3 ??

Any help appreciated

Thanks

Hmm - this (and the sumproduct one) seem to crap out with any blank cells in
the range. Here's the one that works:

=SUMPRODUCT((D1:D100<"")/COUNTIF(D1:D100,D1:D100&""))fromhttp://groups-beta
..google.com/group/microsoft.public.excel.worksheet.functions/msg/d5e376baefe
8898c




"JulieD" wrote in message
...
Hi Alan

use the following ARRAY formula (enter with control & shift & enter, not
just enter)

=SUM(1/COUNTIF(A1:A10,A1:A10))

Cheers
JulieD


"Alan" wrote in message
...
A belated Happy New Year to All

Wondering if it is possible to count the number of unique values in a
column
???

A column contains 5 digit numeric values and I am looking for a method
that
will tell me how many different values are in the column.

ie 23456 34567 56789 23456 56789 = 3 ??

Any help appreciated

Thanks

Dave R. wrote:
Hmm - this (and the sumproduct one) seem to crap out with any blank cells in
the range. Here's the one that works:

=SUMPRODUCT((D1:D100<"")/COUNTIF(D1:D100,D1:D100&""))

[...]

Here is an explanatory post:

http://www.mrexcel.com/board2/viewtopic.php?t=73502