Ron,

Thanks for your post. The setup (format) of the solution is fine, but
in the "Count Of comp" I want only the count of Unique companies. So in
case of Product x would have only 2 (and not 3) as Count of Comp.

Is that possible?

Regards,
HP
India

Ron Coderre wrote:
Try this:

<Data<Pivot Table
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the Prod field here
DATA:
Drag the Comp field here
If it doesn't list as Count of Comp...dbl-click it and set it to Count
Drag the Unit field here
If it doesn't list as Sum of Unit...dbl-click it and set it to Sum

Click [OK]
Select where you want the Pivot Table

Not quite right yet...so....
Click and HOLD the Data column of the Pivot Table
Drag it on top of the Total heading....Release
Done!

That will list each Product with the Count of Companies and Sum of Units.

To refresh the Pivot Table, just right click it and select Refresh Data

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Hari" wrote:

Hi,

If I following data,

Comp Prod Unit
a x 1
b x 2
a x 2
a y 5


Then how can I know the number of unique companies for each type of
product (and the count of units)

I want something like this

Product UniqueComp SumOfUnits
x 2 5
y 1 5

I can do the SumOfUnits using SumIf formula but dont know how to
generate the Unique company counts for each product.

Also is it possible to get a Pivot based solution to this problem?

Regards
HP
India