The Problem: SUM Ignores Your Filter
A common workflow is to filter a transaction list to one category and read off the total. A plain SUM over the Amount column keeps adding every row in its range, including rows the filter has hidden, so the figure does not change when you filter. SUBTOTAL is designed for this situation.
SUBTOTAL Syntax and Function Numbers
Microsoft documents the syntax as SUBTOTAL(function_num, ref1, [ref2], ...), which returns a subtotal in a list or database. ref1 is required, and further references are optional.
The function_num argument selects the calculation. For SUM, the code is 9 or 109. Microsoft states that 1-11 include manually hidden rows, while 101-111 exclude them, and that filtered-out cells are always excluded. In other words, SUBTOTAL ignores rows that are not included in the result of a filter whichever number you use; the two ranges of codes differ only for rows you hid by hand.
Microsoft also says that if a range already contains subtotals, those nested subtotals are ignored to avoid double counting, and that SUBTOTAL returns #VALUE! if any reference is a 3-D reference.
Worked Example with Synthetic Data
Take four synthetic transactions with amounts in C2:C5 (withdrawals negative):
Description Category Amount
Sample Cafe Dining -4.50
Sample Market Groceries -62.30
Sample Cafe Dining -7.25
Sample Books Other -18.00The total of all four rows is -4.50 - 62.30 - 7.25 - 18.00 = -92.05. Two formulas placed below the data:
=SUM(C2:C5)
=SUBTOTAL(109, C2:C5)With no filter applied, both return -92.05. Now filter the Category column to Dining. The SUM formula still returns -92.05, while the SUBTOTAL formula returns -11.75 (-4.50 plus -7.25), because the other two rows are filtered out.
The difference between 9 and 109 shows up when you hide a row manually instead of filtering. If you hide the Sample Market row by hand (with no filter), SUBTOTAL(9, C2:C5) still includes it and returns -92.05, while SUBTOTAL(109, C2:C5) excludes it and returns -29.75 (-92.05 + 62.30 = -29.75).
Microsoft notes SUBTOTAL is designed for vertical ranges of data. Hiding a column does not affect a horizontal subtotal that uses 101 or higher.
Where Totals Should Live
Put the SUBTOTAL formula outside the filtered range, for example in a row below the data, so the filter does not hide the total itself. Microsoft also notes elsewhere in its table documentation that the total row of an Excel Table uses SUBTOTAL by default, so if you have formatted your data as a table, you may not need to write the formula yourself.
Whichever you choose, compare an unfiltered total against the statement's own figures first, so that you know the base data is complete before you rely on filtered views.
Limits
SUBTOTAL answers "what is visible right now". It does not label or group your transactions; if you need all categories side by side, a SUMIFS per category or a PivotTable gives a fixed layout instead. This article does not claim one method suits every workbook.
If your transactions are still locked in a PDF, PDFtoRow converts bank statement PDFs to Excel or CSV and shows a free preview of the first 10 rows.
Convert Bank Statement PDFs Cleanly with PDFtoRow
Tired of fixing broken columns, corrupted dates, and numbers stored as text? PDFtoRow extracts transaction tables directly from digital PDF vector layers, preserving check numbers and outputting true numeric amounts. Statements up to 5 pages are free once per email every 24 hours. Check the 10-row preview before you download.
Frequently Asked Questions
Microsoft states that function numbers 1-11 include manually hidden rows while 101-111 exclude them, and that filtered-out cells are always excluded. For SUM, that means 9 includes manually hidden rows and 109 excludes them.
Yes. Microsoft says SUBTOTAL ignores any rows that are not included in the result of a filter, regardless of which function_num you use.
No. Microsoft states that nested subtotals within the range are ignored to avoid double counting.
Primary Evidence & Official References
- SUBTOTAL function — support.microsoft.com (Verified 2026-10-29). Official source documentation →
- Total the data in an Excel table — support.microsoft.com (Verified 2026-10-29). Official source documentation →