Count unique entries

[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.

 

[Frank Kabel]

Hi
use the following formula
=SUMPRODUCT((D1100<>"")/COUNTIF(D1100,D1100&""))
if D1100 is your range with data

 

[JulieD]

Hi Lynn

on the counting unique entries in a range have a look at
http://www.cpearson.com/excel/duplicat.htm

Chip has an article there on "couting unquie entries in a range"

Cheers
JuileD

 

[Peo Sjoblom]

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

 

[Guest]

Thanks!!

-----Original Message-----
Hi
use the following formula
=SUMPRODUCT((D1100<>"")/COUNTIF(D1100,D1100&""))
if D1100 is your range with data

--
Regards
Frank Kabel
Frankfurt, Germany


.

 

[Kenneth Lam]

Hi
use the following formula
=SUMPRODUCT((D1100<>"")/COUNTIF(D1100,D1100&""))
if D1100 is your range with data

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

Yours is very simple but powerful. Could you briefly explain it? I
really don't understand. Thanks.

 

[Frank Kabel]

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

 

[Aladin Akyurek]

See:

http://tinyurl.com/323w3

]

=SUMPRODUCT((D1100<>"")/COUNTIF(D1100,D1100&""))
if D1100 is your range with data

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

Yours is very simple but powerful. Could you briefly explain it? I
really don't understand. Thanks.

[...]