Pivot Tables AND not OR

  • Thread starter Thread starter carole
  • Start date Start date
C

carole

I have a pivot table, each company may be selected for up to 5 categories,
lets say apples, oranges, bananas, kiwi and grape. I have selected all
companeis who have ticked apples and grape, but the pivot table show all
which have apples or grape or both. I only want to see those which have
both. Any ideas?
 
Hi,

Let's assume the Companies are in column C2:C100 (the data source, not the
pivot table) and the productes in D2:D100 You can create a new column in the
source which might look like this in cell E2. Then copy it down. Add this
field to the pivot table and then filter on it.

=SUMPRODUCT((C2=$C$2:C2)*(($D$2:D2="Grapes")+($D$2:D2="Apples")))>=2
 
Back
Top