SUBTOTAL distinguishes filter-hidden rows from manually hidden rows. Choose the function code according to the population you want to include, not just the aggregation name.
Choose the aggregation and hiding policy
For a sum, =SUBTOTAL(9,C2:C100) excludes rows hidden by an autofilter but includes manually hidden rows. =SUBTOTAL(109,C2:C100) also skips manually hidden values.
Other aggregation codes follow the documented scheme; for example, the versions that skip manually hidden values are not interchangeable with the ordinary count or average codes. Check the current function reference before changing a code.
Test both kinds of hiding
Create a small numeric sample and note its full sum. Filter out one record, then manually hide another. Compare the two formulas to establish which rows contribute to each result.
Do not use a total’s apparent change as proof that records were deleted. Hiding changes visibility, while filtering defines a displayed subset. Neither operation by itself removes the source records.
Avoid double-counting and ambiguous labels
SUBTOTAL ignores nested SUBTOTAL calls inside its referenced ranges to help prevent double-counting. Ordinary SUM formulas or other summary rows are not all automatically equivalent to nested SUBTOTAL outputs.
Keep the input range restricted to intended records and identify the resulting total as filtered, visible-only, or full-population as appropriate. Verify numeric types and new-row inclusion separately. If other collaborators change a shared filter, the subtotal may change too, so record the view or filter conditions when a reported number must be reproducible rather than relying on the current on-screen state alone.
References
Reviewed against the linked official product guidance on November 3, 2026. Features, limits, screen labels, and access can vary by account, platform, region, and organization policy.