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

Hi Thom,


Take a look at Chip's brilliant site :

http://www.cpearson.com/excel/duplic...CountingUnique

HTH
Cheers
Carim

Hi Thom

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

--
Regards

Roger Govier


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

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

--
HTH

Bob Phillips

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

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

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




"Thom" wrote:

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