How to apply multiple filters to multiple value fields in a pivot table?
10,960
Add 2 new columns which will be populated via formula as follows:
- "Florida Count" -
=if( B1 = "Y", 1, 0)
- where column B is "Florida Sales" - "SE Count"
=if( C1 = "Y", 1, 0)
- where column C is "S. East Sales"
Then you will use the pivot to sum these two new columns and you won't need any filtering.
Author by
Trevor D
Fresh out of college Excel: Intermediate/Advanced; Access: Beginner/Intermediate; SQL: Beginner/Intermediate
Updated on June 14, 2022Comments
-
Trevor D over 1 year
I use the following data...
...to create the following pivot table.
I want to apply the filter "FLORIDA SALES = Y" only to the first values column, and the filter "S.EAST SALES = Y" only to the second values column, to produce a pivot tables that looks like this:
I'm using colors here to show that I want each filter to filter only ONE of my value columns. I have 16,592 distinct UPCs so choosing to filter based on UPC is out of the question.