Counting unique values

  • Thread starter Thread starter Howard Pain
  • Start date Start date
H

Howard Pain

If I have a column containing for instance part numbers, how can I count how
many unique part numbers there are?

Many thanks
Howard
 
Hi Howard
try (not counting blank rows as an entry)
=SUMPRODUCT((A1:A1000<>"")/COUNTIF(A1:A1000,A1:A1000&""))
 
Howard

From Chip Pearson's website on counting unique values.........

http://www.cpearson.com/excel/duplicat.htm#CountingUnique


Counting Unique Entries In A Range

To count the number of unique entries in the list, use the following array
formula:

=SUM(IF(FREQUENCY(IF(LEN(Range1)>0,MATCH(Range1,Range1,0),""),
IF(LEN(Range1)>0,MATCH(Range1,Range1,0),""))>0,1))

This will return the number of unique entries in Range1. It will not count
blanks at all.



The formula above will return 3, indicating that there are 3 unique,
non-blank entries in the range (Able, Baker, and Cathy). Click here for more
information about Array Formulas.

If your data does not have any blanks in the range, you can use the following
Array Formula, developed by David Hager:

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

If your data has only numeric values or blank cells, with no text or string
values, you can use the formula

=SUM(N(FREQUENCY(A1:A10,A1:A10)>0))

to count the number of unique values. This will count only the number of
unique numeric values, not including text values.


Gord Dibben Excel MVP
 
Back
Top