Subtotal Disappear When Filter

[gnulab]

Hi,

I’m encountering an issue where the Subtotal row disappears when a filter is applied.

When the filter is set to “All”, the subtotal row is visible. However, when I filter the data (showing only specific values), the subtotal row disappears.

This behavior is consistent across Excel 2007, Office 365, and when saving in ODF format. Attached is picture of the problem I’m experiencing.

Interestingly, this does not happen in Excel—the subtotal row remains visible even when filters are applied.

Has anyone experienced this before or knows why this happens?

Thanks.
Henry

[EarnestAl]

Exclude the subtotal from the range that you are filtering.
Personally, I tend to put the subtotal above the column headings because I can’t be bothered scrolling down just to see the subtotal.

[sokol92]

@gnulab

this does not happen in Excel

Please upload a small example of such a file in .xlsx format and we will solve this mystery together.

[gnulab]

Here it is.

11 - Copy.xlsx (11.4 KB)

[karolus]

this should fit your needs:
132896_subtotal_above.ods (30.2 KB)

[gnulab]

Placing the subtotal above the dataset avoids the issue, but this feels more like a workaround than a proper solution. Another workaround is leaving several blank rows (e.g., three rows) between the last data row and the subtotal row.

However, the subtotal row should ideally remain visible when a filter is active. Its disappearance is counterintuitive, even though it occurs because the filtered column in the subtotal row is empty and therefore does not match the filter criteria.

[sokol92]

@gnulab

Here it is.

Thank you.
In your example, cell D144 contains a number, not a formula. In the context of this discussion, Excel and Calc behave the same.

1. We usually separate the totals row from the summed range with a blank line because the AutoFilter region automatically expands downward to capture rows that contain at least one non-blank cell in the columns of the filtered range.
2. In all cases, it’s better to use AGGREGATE rather than SUBTOTAL.

[Villeroy]

11 - Copy.xlsx (26.8 KB)
Choose the right area and group by “Locasi” (the only category which makes sense).