Hi Lawrence

You will need to add another column to your source table Named Count
In D2 enter
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=$B2)*($C$2: $C2=C2))=1,1,0)
and copy down

Add the Count Column to the source range for your PT, and use Sum of Count
as your data field

--
Regards
Roger Govier

"LawrenceHG" wrote in message
...
I need some help getting a particular result in a Pivot Table.

I have an Excel list of 19,000 purchase orders. Included in each purchase
order record (row) is the vendor name as well as the department and
division
of the company that placed the purchase order. (Each division is made up
of
numerous departments.)

I'm trying to answer the following question: How many departments in each
Division used a particular vendor? Please note that I'm *not* looking for
the
number of orders placed with each vendor--I'm looking for the number of
departments that placed at least one order.

This is best illustrated with an example. Imagine this set of data. (If
this
is hard to read, copy to a text editor with fixed-width font--my
apologies.)

Vendor Department Division
Alpha Sales Asia
Alpha Sales Asia
Alpha Finance Asia
Alpha Finance Asia
Alpha Finance Asia
Alpha Finance Europe
Beta Sales Asia
Beta Mfg Europe

I want to produce the following Pivot Table

Sales Finance Asia Div Tot Finance Mfg Europe Div
Tot Grand Total
Alpha 1 1 2 1 1
3
Beta 1 1 1 1
2

I cannot figure out how to do this in a Pivot Table.

If I set the department as the data field of the Pivot Table and use the
count function, I get the number of purchase orders each department
places.
Not what I want. If I use a function that gives me "1"s in the data cells
(e.g., adding a field of "1"s to the database and using the "Max" function
to
aggregate data), then the PivotTable won't use the Sum function to create
the
subtotals and Grand Total (I get "1"s there as well).

In a Pivot Table, is it possible to use a different function to total the
rows than is used to produce the individual values in the rows?

Can anyone suggest a Pivot Table method that will produce the results I
seek? Outside the Pivot Table world, I think I can do it with repeated
applications of the SUBTOTAL command, but it would take too long to
execute
SUBTOTALS with 19,000 records.

Any help is appreciated.