Count unique items in groups

[GarToms]

Hi all,

I'm looking to count unique items in column 2 within the groups i
column 1.
e.g.
A......B
X......100
X......100
Y......200
Y......100
Y......200
Z......500

Required Output:
Y=2 (because of 100 and 200)
X=1 (because only 100)
Z=1 (because only 500)

I am totaly lost to find a solution so any help would be great.
Note i can't use a pivot due to the size of my worksheet.

GarTom

 

[Kimbo]

Give you columns headings eg: Letter & Amount then use Data>Subtotal - In the
"At each change in" box select "Name" then "Use Function" of Count then "add
subtotal to" the "Amount".

In the left of the screen you will see the numbers 123. Click on the 2 and
this will shrink the data to show totals only. You can then copy those
visable cells only to another sheet. Or alternatively you could research
"Pivot Tables" at the following site.
http://www.contextures.com/tiptech.html

 

[DOR]

Assume data are in A2:B7. Use helper column C. In C2 enter

=A2&B2 and copy down to C7.

Enter X,Y, Z in D2 to D4. In E4 enter

SUMPRODUCT(--($A$2:$A$7=D2),1/COUNTIF($C$2:$C$7,$C$2:$C$7))

and copy down to E4.

HTH

Declan