Count unique items in range

[Thom]

Hi,

Can someone help me with a formula to count unique items in a range (incl
blank cells) ?

Example,

A B C
1 xx zz yy
2 zz xx
3 qq xx

I would like to see the result of the formula as,

A B
1 qq 1
2 xx 3
3 yy 1
4 zz 2

I've tried all the combinations of Count and Frequency with no success.
Should I use another function ?

Thanks for your help in advance,

Thom

 

[Carim]

Hi Thom,


Take a look at Chip's brilliant site :

http://www.cpearson.com/excel/duplicat.htm#CountingUnique

HTH
Cheers
Carim

 

[Roger Govier]

Hi Thom

I entered your list of items in E1:E4
In F1
=COUNTIF($A$1:$C$3,"="&E1)
and copy down

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[CLR]

I use Jim Cone's fine commercial add-in for jobs like this.......it works
well. It's available at

http://www.realezsites.com/bus/primitivesoftware/

Vaya con Dios,
Chuck, CABGx3