Countif unique

B

Bruce

How do i count unique values in my data?

Eg A1:A5
Red
Blue
Yellow
Blue
Red

=3

Bruce
 
B

Bruce

Thank Gary's Student.

What is I add the following criteria....With this formula it returns #div/0
if there are blanks.

Reason is my actual data has a dynamic range refreshed by MSQuery to a DB. I
want to set the range in the count to A1:A1000 to cover the maximun records I
except, however there may only be 300 or so actual records to count....

Bruce
 
B

Bob Phillips

=SUMPRODUCT((A1:A5<>"")/COUNTIF(A1:A5,A1:A5&""))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Top