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" <(E-Mail Removed)> wrote in message
news:2921A78E-E4E3-4269-B87E-(E-Mail Removed)...
> 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