Count Num of Unique items in col ?

[NaplesDave]

I need to be able to count the number of times an item Uniquely appears in a
column. IE:
Result cell NAMED: Species
Contents of column:
CAT
CAT
DOG
CAT
FISH
FISH

Species should = 3

How can I do this function in EXCEL 2003?

 

[Gary''s Student]

=SUMPRODUCT((H1:H1500<>"")/COUNTIF(H1:H1500,H1:H1500&""))

for data in column H.

Adjust to suit

 

[CindyC]

=SUMPRODUCT((H1:H1500<>"")/COUNTIF(H1:H1500,H1:H1500&""))

for data in column H.

Adjust to suit

 

[CindyC]

Ok. Thanks. Now, how do I count the number of unique items by 3 other
criteria
ie
number of cats
sold at location1
by ?salesperson
by date

 

[Jacob Skaria]

Dear Dave

Assuming your data is in Col A use the below formula

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

If this post helps click Yes

 

[Sheeloo]

Can you pl. explain how this works?

=SUMPRODUCT((H1:H1500<>"")/COUNTIF(H1:H1500,H1:H1500&""))

for data in column H.

Adjust to suit

 

[T. Valko]

Can you pl. explain how this works?

=SUMPRODUCT((H1:H1500<>"")/COUNTIF(H1:H1500,H1:H1500&""))

Try this...

A1 = A
A2 = B
A3 = B
A4 = C
A5 = D

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

Result is 4

Break it down into individual calculations.

Enter this formula in C1 and copy down to C5:

=A1<>""

Enter this formula in D1 and copy down to D5:

=COUNTIF(A$1:A$5,A1&"")

Enter this formula in E1 and copy down to E5:

=C1/D1

Enter this formula in F1:

=SUM(E1:E5)

That all makes sense, doesn't it? OK, delete the entry in A4. Now the result
is 3 and it still makes sense.

Ok, change the formula in D1 to:

=COUNTIF(A$1:A$5,A1)

Copy down to D5 and see what happens to those summary formulas.

Concatenating the empty text string ("") prevents the #DIV/0! error when
there are empty cells.

If there were no empty cells then you could use:

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

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

Seems to have evolved as the "standard".


expbiff101

 

[Sheeloo]

Thanks Biff (thanks to Jacob too),

I get it now. Beautiful...
You are essentially dividing the count of each different number from 1 so
that you get the unique count when you sum them up...

I had tried to understand the same way but my mistake was that I entered 5
different characters down to row 30 .... obscuring the meaning.

Thanks for the wonderful way you explained it.

 

[T. Valko]

I love explaining things! I think I'd make a good teacher.

 

[Bernd P]

Hello,

I suggest to use my UDF Pfreq:
http://www.sulprobil.com/html/pfreq.html

IMHO Sumproduct is highly overrated:
http://www.sulprobil.com/html/sumproduct.html

If you just need to count unique entries of a list see this comparison
of approaches, please:
http://www.sulprobil.com/html/count_unique.html
[Keep in mind that the chart shows logarithmic scales, please!]
The approach with Sumproduct divided by Countif is one of the worst
and therefore found a place on my list of Excel Dont's:
http://www.sulprobil.com/html/excel_don_ts.html

Regards,
Bernd

 

[Jacob Skaria]

For sure....

If this post helps click Yes

 

[Sheeloo]

Yes, you will.

I also enjoy teaching and that is my long term career goal.