A total beneath a filtered list can be misleading if it still includes records you cannot see. Decide whether the figure should describe every record or only the filtered selection, and label it accordingly.

For a vertical list in desktop Excel, SUBTOTAL can calculate a sum that excludes rows removed by a filter. The function number also determines what happens to rows hidden manually, which is a separate action from filtering.

Choose the hidden-row rule explicitly

For amounts in B2:B20, =SUBTOTAL(9,B2:B20) sums the filtered selection but still includes manually hidden rows. Using 109 instead of 9 also excludes manually hidden rows. Both versions exclude filtered-out rows.

Place the formula outside the range it totals and keep its label visible. Describe the intended rule, such as “Filtered total, excluding manually hidden rows,” rather than simply “Total” when several interpretations are possible.

SUBTOTAL is designed for vertical ranges. Hiding a column is not the equivalent of hiding a row for this purpose, so do not apply the same expectation to a horizontal collection of amounts.

Prove the result with a tiny sample

In a separate test sheet, enter three fictional records with amounts 10, 20 and 30. With all rows visible, the sum is 60. Filter out the record worth 20; both sum variants should show 40.

Clear the filter, then manually hide the row worth 20. The version using 9 should remain 60, while the version using 109 should show 40. Unhide the row after the test.

Apply the chosen rule to the real list and compare one filtered group with an independent calculation. Confirm that the formula covers the newest records and excludes unrelated notes or totals. When exporting or printing, keep the filter context with the reported figure so another reader knows which records it represents.

References: Microsoft: SUBTOTAL function.