Hi, I have a pivot table that sums the values of Column A from multiple spreadsheet rows to aggregate them across the value of another Column B. This works great. However, I would like to exclude from these sums any rows where a third Column C has a certain value.
When I use the filter option for the pivot table to filter out this value of Column C, it affects the output sum rows in ways that do not make sense to me (sums are reduced even for values of Column B whose records do not include any instances of the excluded Column C value).
I also tried adding Column C as an output row instead of a filter, but the aggregation still didn’t add up in a way that made sense to me, my best guess is it seemed like a blank value might have been treated as “repeat last-known value” maybe when doing the sum and aggregation.
Any advice about how to do this, or what I’m doing wrong here?
Example source:
A,B,C
Cat1,23,
Cat1,2,exclude
Cat3,5,
Cat4,8,
Cat3,9,
Cat3,2,
Cat3,13,exclude
Cat3,2,
Example of what I want the destination pivot table to look like:
A,Sum B
Cat1,23
Cat3,18
Cat4,8