Ron,

Thanks again for the post.

Problem with putting the count function to right of the Total table is
that I would have to adjust the formula ranges for each product typr
manually which would defeat the purpose of making a pivot table.

Are there pure formula based solutions to this problem (without using
pivots). Probably some variation of an array sumproduct along with
"embedded" countif kind of formula?

Regards,
HP
India

Ron Coderre wrote:
Yikes....I completely missed that!

As far as I know, there's no "count unique" functionality in a Pivot Table.

Maybe this will work for you...

When builiding the Pivot Table, put the Company field in the COLUMN section
(and remove it from the DATA section)
-That will list the companies in the column headings

Then, put the COUNT function to the right of of the Grand Total column to
count the number of cells under the Company listings that have numbers in
them...and, consequently, the count of companies for each product.

Note: if the GetPivtoData function appears and you don't want to use it...
See the instructions on how to toggle it on/of at Debra Dalgleish's website:
http://contextures.com/xlPivot06.html

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP


"Hari" wrote:

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