ExcelBanter - Countif unique

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Countif unique (https://www.excelbanter.com/excel-worksheet-functions/74571-countif-unique.html)

How do i count unique values in my data?

Eg A1:A5
Red
Blue
Yellow
Blue
Red

=3

Bruce

=SUMPRODUCT(1/COUNTIF(A1:A5,A1:A5))
--
Gary''s Student

"Bruce" wrote:

How do i count unique values in my data?

Eg A1:A5
Red
Blue
Yellow
Blue
Red

=3

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

"Gary''s Student" wrote:

=SUMPRODUCT(1/COUNTIF(A1:A5,A1:A5))
--
Gary''s Student

"Bruce" wrote:

How do i count unique values in my data?

Eg A1:A5
Red
Blue
Yellow
Blue
Red

=3

Bruce

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bruce" wrote in message
...
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

"Gary''s Student" wrote:

=SUMPRODUCT(1/COUNTIF(A1:A5,A1:A5))
--
Gary''s Student

"Bruce" wrote:

How do i count unique values in my data?

Eg A1:A5
Red
Blue
Yellow
Blue
Red

=3

Bruce