Count unique items in groups
 

Hi all,

I'm looking to count unique items in column 2 within the groups in 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.

GarToms

Count unique items in groups
 

Give you columns headings eg: Letter & Amount then use DataSubtotal - 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

"GarToms" wrote:


Hi all,

I'm looking to count unique items in column 2 within the groups in
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.

GarToms




--
GarToms

Count unique items in groups
 

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

GarToms wrote:
Hi all,

I'm looking to count unique items in column 2 within the groups in
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.

GarToms




--
GarToms