How to apply multiple filters to multiple value fields in a pivot table?

2
votes

I use the following data...

Sample Table

...to create the following pivot table.

Sample 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:

enter image description here

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.

1
excelpivot-table

1 Answers

2
votes

Add 2 new columns which will be populated via formula as follows:

  1. "Florida Count" - =if( B1 = "Y", 1, 0) - where column B is "Florida Sales"
  2. "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.