Count Num of Unique items in col ?
 

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?

Count Num of Unique items in col ?
 

=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?

Count Num of Unique items in col ?
 

"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?

Count Num of Unique items in col ?
 

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

"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?

Count Num of Unique items in col ?
 

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
---------------
Jacob Skaria


"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?

Count Num of Unique items in col ?
 

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?

Count Num of Unique items in col ?
 

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?

Count Num of Unique items in col ?
 

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?

Count Num of Unique items in col ?
 

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?

Count Num of Unique items in col ?
 

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

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?

Count Num of Unique items in col ?
 

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