--- title: "You Filtered the Rows. The Total Stayed Put." author: "Yosef Gerson" published: "2026-11-16T06:53:02.499Z" updated: "2026-11-16T06:53:02.499Z" url: "https://zyvop.com/you-filtered-the-rows-the-total-stayed-put-xj9iz" canonical: "https://zyvop.com/you-filtered-the-rows-the-total-stayed-put-xj9iz" tags: ["excel","spreadsheets","google-sheets"] --- # You Filtered the Rows. The Total Stayed Put. Give a spreadsheet total a clear job before you trust it. ![Cover image]() By Yosef Gerson ![Yosef Gerson at a desk with a spreadsheet]() You filter an order sheet to show only the deliveries that went out. Three rows remain. Above them, a tidy total says $1,500. The three visible amounts add up to $720. The addition may be fine. The total may still be reading all six orders, including the ones the filter hid. The sheet changed what you could see without changing what that formula was asked to count. Send that view in an email and someone could reasonably read $1,500 as the delivered total. The filter makes it look like the number belongs to those three rows. ### Make the mismatch visible Paste this example into cells A4:C10 of a blank sheet. Each row represents one order, and the amount is its order value. | Order | Status | Amount | | --- | --- | --- | | O101 | Delivered | 240 | | O102 | Pending | 160 | | O103 | Delivered | 300 | | O104 | Cancelled | 220 | | O105 | Pending | 400 | | O106 | Delivered | 180 | Keep the summary above the filtered range so hiding rows cannot hide the totals too. In E1, type **All listed orders**. In F1, enter: ```excel =SUM(C5:C10) ``` In E2, type **Visible orders**. In F2, enter: ```excel =SUBTOTAL(109,C5:C10) ``` Both initially show 1,500. Format them as currency if you prefer. Select A4:C10, turn on a filter, and keep only **Delivered** in the Status column. The first result stays at 1,500. The second becomes 720: 240 + 300 + 180. Clear the filter, and the second returns to 1,500. The formulas work in Excel and Google Sheets with English function names and comma separators; your locale may use semicolons. The fixed range is deliberate for this six-row exercise. If you add records beyond row 10, expand the range rather than assuming they were included. ### Keep both answers when both questions matter It is tempting to replace every `SUM` with `SUBTOTAL`. I would first ask what the reader needs. A total for the whole list should not change every time someone explores a filter. A total meant to describe the filtered selection should. Showing both, with explicit labels, gives you a useful comparison rather than an unexplained disagreement. Here, filtering to Pending gives a visible value of $560. Filtering to Cancelled gives $220. Those two groups plus Delivered account for the $1,500 whole-list value. There is another problem worth noticing: the whole list includes a cancelled order. Even a perfectly calculated grand total would be a poor answer to “How much did we deliver?” The formula cannot decide whether the question calls for delivered orders, active orders, collected cash or something else. The label and the inclusion rule have to do that work. ### Why the formula says 109 In `SUBTOTAL`, 109 selects a sum that excludes filtered-out and manually hidden rows. Code 9 also excludes rows removed by a filter, but includes rows hidden by hand. Both Microsoft and Google document that distinction. [Microsoft reference](), [Google Sheets reference](). To compare them, put **Filtered orders** in E3 and `=SUBTOTAL(9,C5:C10)` in F3. Keep the Delivered filter, then manually hide row 7, containing O103 and its $300 amount. The `109` result becomes $420. The `9` result stays at $720. Neither setting is universally right. If manual hiding is just tidying the screen and must not change the report, code 9 may better match that rule. If the summary must describe only the rows actually shown, 109 is the more direct fit. Choose the rule you actually want. Hiding rows should not become an undocumented way to adjust a report. Unhide O103 before continuing. ### Leave a trail for the next reader A changing number is harder to trust when the reason is off-screen. Before sharing the sheet, include enough context to reconstruct the view: - A label stating whether the total covers the whole list or the visible selection. - The filter applied, such as “Status: Delivered.” - The range or table the formula uses. - A small check showing the expected result for one known selection. For this example, “Delivered orders, O101/O103/O106, $720” is a simple check. For a larger sheet, use a small subset you can independently reconcile. Do not describe the number of visible rows as a count of orders unless one row really is one order. A line-item export needs a different count. Keep detail rows and summary rows separate, too. `SUBTOTAL` skips other `SUBTOTAL` formulas inside its range, but that does not make an arbitrary sheet of manually entered subtotals safe from double-counting. [Google Sheets reference](). ### A quick test before you send it Pick a filter that leaves a total you can add yourself. Apply it, clear it and apply it again. Then check the label beside the result. If you send a view of delivered orders, the number beside it should describe delivered orders. If it describes the whole list, say so. That small label can save the next person from trusting the wrong answer.