Count Num of Unique items in col ?

For sure....

If this post helps click Yes
---------------
Jacob Skaria


"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


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

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
--
Biff
Microsoft Excel MVP


"Sheeloo" wrote in message
...
Can you pl. explain how this works?

"Gary''s Student" wrote:

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

for data in column H.

Adjust to suit
--
Gary''s Student - gsnu200849


"NaplesDave" wrote:

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?

Yes, you will.

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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP