You could add a 4th column that indicates the number of companies from which
the client has made purchases. Let's say you use column D for that.

Assuming headers in row 1, data in rows 2:100, in D2 put this formula and copy
down through D100:


=IF(SUMPRODUCT(--($A$2:$A$100=A2),--($B$2:$B$100=B2))=COUNTIF($A$2:$A$100,$A2),1,2)

Then add that field to the Pivot Table (maybe as a page field?) and display
only the rows where the value is 2.


On 26 Jan 2005 07:33:09 -0800, (Gavim Francis) wrote:

Hi, I've just about reached the limit of my knowledge on this query
and I'm hoping someone can help me on it.
In column A I have a list of individual client sales e.g -

client aardvark
client aardvark
client bear
client cat
client cat
client cat
client camel
client camel

Column B lists the name of the company that sold the product to the
clients in column A. Column B will only list 1 of 2 companies. Both
companies could have sold to the same client but not always.

Column C shows the sale amount.

I've created a Pivot table which lists the sum total prices of the
sales to the clients in Column A by the companies in Column B. This
is fine but I'd like to compare the total prices when Company X and
Company Y have sold to Client Bear for example. This can be shown in
the Pivot table but it also lists when only Company X (not company Y)
has sold to client Camel.

So basically I'd like some way to produce a total of all sales by
Company X when they have sold to the same client as Company Y. I'm
thinking that Vlookup is needed but I'm really not sure where to go.

Cheers for any help.