Tutorial

You Filtered the Rows. The Total Stayed Put.

Give a spreadsheet total a clear job before you trust it.

Yosef Gerson
Spreadsheet and data writer
·
3 min read
·
AI-assisted

AI tooling disclosure

Generative AI materially contributed to this article.

Human review confirmed by the author.

You Filtered the Rows. The Total Stayed Put.

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:

=SUM(C5:C10)

In E2, type Visible orders. In F2, enter:

=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.

Comments(0)

Join the discussion by logging into your account.

No comments yet. Be the first to comment!

Yosef Gerson
Yosef Gerson

Spreadsheet and data writer

I’m Yosef Gerson. I write practical guides about spreadsheets, data and the small details that can change what a number actually means. I’m interested in the gap between a result that looks right and one you can trust. My articles work through real spreadsheet questions step by step, with examples you can follow and checks you can use on your own work.

Subscribe to Yosef Gerson's Newsletter

Direct email dispatches when new stories are published. Zero algorithms.