Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivot Tables AND not OR
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? |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivot Tables AND not OR
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 -- Thanks, Shane Devenshire "carole" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Distributing Pivot Tables where pivot table data can't be accessed | Excel Discussion (Misc queries) | |||
Building pivot tables in Excel 2007 based on existing pivot tables? | Excel Discussion (Misc queries) | |||
Pivot Table Data Adding contents of two pivot tables and param que | Excel Discussion (Misc queries) | |||
Average Calculations from Pivot Tables - Get Pivot Data? Calc Fiel | Excel Worksheet Functions | |||
How does the term 'pivot' apply to Excel's Pivot tables and Pivot. | Excel Discussion (Misc queries) |