Count unique entries

  • Thread starter Thread starter Lynn
  • Start date Start date
L

Lynn

1)How can I count unique entries within a column? Eg:

APPLES
APPLES
ORANGES
ORANGES
APPLES
BANANAS

The answer I want is 3 - 3 unique entries in the list.

2)If these entries are part of a large datalist - how can
I count unique entries in a category within a category? A
pivot table scenario would be great but it only seems to
allow count - not a unique count.
 
Hi
use the following formula
=SUMPRODUCT((D1:D100<>"")/COUNTIF(D1:D100,D1:D100&""))
if D1:D100 is your range with data
 
Another way would be to apply an advanced filter
data>filter>advanced filer, unique entries only, then use

=SUBTOTAL(3,Range)

where Range is the range with fruits (header excluded)
to turn off filter data>filter>show all


--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Thanks!!
-----Original Message-----
Hi
use the following formula
=SUMPRODUCT((D1:D100<>"")/COUNTIF(D1:D100,D1:D100&""))
if D1:D100 is your range with data

--
Regards
Frank Kabel
Frankfurt, Germany


.
Click to expand...
 
Hi
use the following formula
=SUMPRODUCT((D1:D100<>"")/COUNTIF(D1:D100,D1:D100&""))
if D1:D100 is your range with data
Click to expand...

I will use the array formula
{=SUM(1/COUNTIF(D1:D100,D1:D100))}

Yours is very simple but powerful. Could you briefly explain it? I
really don't understand. Thanks.
 
Hi Kenneth
both formulas are quite similar. Advantage of SUMPRODUCT: You don't
have to array enter it. Also your formula counts blanks cells as well
as one unique entry
 
Back
Top