Why does my Excel total stay the same after filtering?
Filtered down to Sales, the sheet still shows a total that adds up every department. Which formula counts only the rows on screen?
For a total of the currently visible entries in a vertical list, use =SUBTOTAL(109,D2:D7). The equivalent SUM formula keeps adding numbers in D2:D7 when some of those rows disappear from the screen. If rows you deliberately hide should still count, use 9 instead of 109. Microsoft documents both SUBTOTAL options.
In a real r/excel thread, a user wanted a sum of the results left by several changing filters. Another participant wanted to keep a total after changing the filter. SUBTOTAL follows the department currently selected on screen. A separate SUMIFS cell can keep showing completed Sales business while the user inspects Support transactions. The original question and follow-up describe both uses.
Reproduce the mismatch with six transactions
Enter this fictional ledger in A1:D7 on a new worksheet. The amounts are generic currency units, entered as numbers. The first column below gives worksheet row numbers for orientation; do not paste it as an extra data column.
| Row | A: ID | B: Department | C: Status | D: Amount |
|---|---|---|---|---|
| 2 | T-01 | Sales | Complete | 120 |
| 3 | T-02 | Support | Complete | 80 |
| 4 | T-03 | Sales | Pending | 60 |
| 5 | T-04 | Sales | Complete | 40 |
| 6 | T-05 | Support | Pending | 90 |
| 7 | T-06 | Sales | Complete | 30 |
Select A1:D7, choose Data → Filter, and open the Department dropdown. Clear Select All, select Sales, and confirm. Rows 3 and 6 disappear because their department is Support. Their values remain in the worksheet. Filtering additional columns narrows the remaining records further. Microsoft’s filtering instructions cover ranges and tables.
Place the following formulas in G10, G11 and G12. Label them in column F as All transactions, Filtered including manual hides, and Filtered excluding manual hides. Keep this summary outside rows 2–7: placing it to the right of the ledger is not enough, because hiding a worksheet row also hides summary cells on that row.
=SUM(D2:D7)
=SUBTOTAL(9,D2:D7)
=SUBTOTAL(109,D2:D7)The expected results are 420, 250 and 250. The four Sales entries add up to 120 + 60 + 40 + 30. Dragging through visible cells does not necessarily give you a formula listing only those individual addresses: read the reference in the formula bar. D2:D7 still names a continuous range. SUM adds the values in its references.
Keeping the 420 total can be useful. A report may need the full ledger beside the current selection. Give each cell a descriptive label so that someone copying a figure into a presentation knows whether it covers the whole business or the department on screen.
Try one more selection before hiding any rows: leave Sales selected and filter Status to Complete. Both SUBTOTAL results become 190. Now switch Department to Support while leaving the status filter in place. The result is 80, not the 170 total of all Support transactions. The Pending entry is still excluded by the second filter. Clear the Status filter to recover the full department selection, then return Department to Sales for the next exercise.
Hide one Sales row and compare 9 with 109
Leave the Sales filter applied. Right-click worksheet row 5 and choose Hide. The 40-unit transaction disappears. G11 should remain 250, while G12 should become 210.
| Ledger state | SUM | SUBTOTAL 9 | SUBTOTAL 109 |
|---|---|---|---|
| All six rows displayed | 420 | 420 | 420 |
| Filtered to Sales | 420 | 250 | 250 |
| Sales filter plus manually hidden row 5 | 420 | 250 | 210 |
The smaller answer is not automatically the correct business figure. If someone hid old transactions merely to tidy the display, excluding those transactions changes the meaning of the report. If the request is specifically to add the three entries still on screen, 210 is the intended result.
For a recurring rule such as excluding cancelled orders, record that status in a column. A hidden row does not explain why it was hidden. A colleague can reveal it without knowing whether the original author intended to change the calculation.
This technique concerns vertical lists. Hiding a column inside a horizontal reference such as B2:G2 does not make SUBTOTAL 109 omit that column. Before continuing with the example, clear the filters and unhide the manually hidden row so that all six transactions are visible again.
Make the total follow an expanding table
A fixed reference ending at D7 can miss a new transaction entered below it. Convert the ledger into an Excel table when you expect it to grow. Select A1:D7, choose Home → Format as Table, pick a style, and confirm the range and header checkbox. The table creation instructions apply to actual Excel tables, not just cells with coloured borders.
Click inside the table, enable Total Row on the table design tab, and choose Sum from the Amount total’s dropdown. The default sum uses SUBTOTAL 109; with these headers, it can appear as =SUBTOTAL(109,[Amount]). Existing total rows can contain custom formulas, so inspect the formula rather than assuming that every bottom row behaves identically. Microsoft’s Total Row guide shows the generated formula.
To keep a summary elsewhere, name the table Transactions in the Table Name box and use =SUBTOTAL(109,Transactions[Amount]). The column reference adjusts as data is added to or removed from the table. Structured references also make the source easier to identify than an unexplained cell range.
Data pasted outside the table still needs attention. In this example, adding a 10-unit Sales transaction as a table row should bring the Sales total to 260, with no other filters or manual hides. If it stays at 250, check whether the new entry belongs to the table, whether another column excludes it, and whether the summary still uses the old D2:D7 reference.
Keep the Sales figure while inspecting Support
Return to the original six transactions for this calculation. Suppose the report must always show completed Sales business even while you inspect Support records. Put the criteria into SUMIFS:
=SUMIFS(D2:D7,B2:B7,"Sales",C2:C7,"Complete")The expected result is 190: T-01, T-04 and T-06 contribute 120 + 40 + 30. The 60-unit Sales transaction is Pending, so it does not qualify. Changing only the screen filter to Support does not change these criteria.
SUMIFS starts with the amount range, followed by pairs of criteria ranges and criteria. Keep those ranges aligned with the same transaction rows and give them matching dimensions. The SUMIFS reference explains the argument order and range requirements.
For the named table, the corresponding formula is =SUMIFS(Transactions[Amount],Transactions[Department],"Sales",Transactions[Status],"Complete"). It will still update when a qualifying transaction changes or enters the table. Filter independence does not freeze yesterday’s reported number.
For example, a new 10-unit Sales transaction marked Complete brings this figure to 200. Mark the new transaction Pending instead, and the completed figure stays at 190 while total Sales business reaches 260. Those two cells now describe different stages of the same department’s work.
If the reporting requirement is to preserve the submitted figure, retain that value separately with its criteria and cutoff. “Complete Sales transactions, source as of October 27, 190 units” says what the snapshot represents. Keep the live formula for subsequent calculations without presenting it as an unchanging record of an earlier submission.
Find the missing amount before replacing more formulas
A correct filter can still produce an unexpectedly small total when amounts are stored as text. SUM ignores text in referenced cells. For a flagged amount, select the cell’s error menu and choose Convert to Number. Alternatively, use =VALUE(D2) in a spare column and fill down to inspect the conversion before replacing the original amounts. Microsoft documents both conversion methods, including pasting the converted results back as values.
Apply conversion to the amount field, not indiscriminately to identifiers. In the small ledger, a shortfall of exactly 120 points you toward T-01. A shortfall matching only the newest transaction suggests checking the range boundary. These comparisons narrow the investigation without rewriting an entire workbook.
Also inspect any intermediate summary rows. SUBTOTAL skips nested SUBTOTAL results, but a typed subtotal or an ordinary SUM result is not automatically excluded just because its label says “Subtotal.” Mixing those amounts with transaction rows can count the same business twice.
Finally, keep the formula cell outside its own input range. A whole-column reference placed in that same column can include itself. Use a separate summary area or the table’s Total Row, reapply filters after relevant data edits, and retain the selection criteria beside any figure you submit.