How to filter before summing in a pivot table?

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

Hi @cds welcome to the Collabora Online forum :slight_smile:

Thanks for the clear example. It makes the problem easy to follow, and your guess about the blank values is very close.

Why the sums look wrong

The pivot table has an option called “Identify categories”. When it is on, every empty cell in the source data takes the value of the cell above it in the same column. In your data, the first “exclude” in column C is copied down into every blank cell below it. So for the pivot table, nearly every row after “Cat1,2,exclude” looks like an “exclude” row. When you filter out “exclude”, those rows go too, and the sums for Cat3 and Cat4 drop even though their own rows have no value in column C. This also explains what you saw when you put column C in the row area: the blanks showed up as the value from the row above.

What to do

  1. Click any cell inside the pivot table.
  2. Open Data > Pivot Table > Insert or Edit… (in the tabbed view: the Data tab, then Pivot Table).
  3. In the Options section of the dialog, clear the “Identify categories” check box.
  4. Keep column C in the Filters area, click OK, and in the C filter of the pivot table untick “exclude”.

With “Identify categories” off, the blank cells stay empty (shown as “(empty)” in the filter list), and you should get Cat1 23, Cat3 18 and Cat4 8.

If you only need the totals and not a full pivot table, a formula also works and never fills blanks. For example, for Cat3:

=SUMIFS(B:B; A:A; "Cat3"; C:C; "<>exclude")

If your spreadsheet uses commas between function arguments, replace the semicolons with commas.

If the totals are still off with that option cleared, please tell me the version from Help > About and, if you can, attach a small sample file, and I will take a closer look.

Thanks
Darshan

Thank you Darshan! This solved my problem.