How to sum only visible cells in Excel

Why SUM includes hidden rows

SUM adds every number in its range. It has no idea whether a row is hidden, filtered out or collapsed in an outline. So when you filter this sales list to the North region and look at a SUM below it, the total does not change: it still shows all ten orders.

That is by design, and sometimes it is what you want, for example a grand total that must not change when someone plays with the filter. When you want the total of what is on screen, you need SUBTOTAL or AGGREGATE.

SUBTOTAL: 9 vs 109

SUBTOTAL's first argument picks the calculation: 9 is SUM, 1 is AVERAGE, 3 is COUNTA, 4 is MAX and so on. Add 100 and you get the same calculation, but it also skips rows you hid by hand. Rows filtered out by a filter are always skipped, whichever number you use.

So =SUBTOTAL(9,E2:E11) changes when you filter for North, but if you also right-click the Cancelled rows and choose Hide, it still adds them. =SUBTOTAL(109,E2:E11) leaves out both. Use 109 unless you have a reason not to, because people hide rows to say "ignore these", and 109 respects that. Both versions also skip other SUBTOTAL formulas inside the range, so subtotal rows within a list are not counted twice.

1. Filter the list: select a cell in it and press Ctrl+Shift+L, then pick North in the Region drop-down.
2. Below the Amount column, type =SUBTOTAL(109,E2:E11). The result now changes every time you change the filter or hide rows.
3. Shortcut: with a filter on, AutoSum (Alt+=) inserts a SUBTOTAL(9,...) formula instead of SUM. Edit the 9 to 109 if you also hide rows by hand.
4. In an Excel table, Table Design > Total Row adds SUBTOTAL(109,...) for you.

AGGREGATE: visible rows and no errors

SUBTOTAL returns an error if any cell in the range holds one, such as a #N/A from a lookup. AGGREGATE (Excel 2010 and later) works like SUBTOTAL but has an options argument. =AGGREGATE(9,5,E2:E11) sums while ignoring hidden rows; =AGGREGATE(9,7,E2:E11) ignores hidden rows and error values; =AGGREGATE(9,6,E2:E11) ignores only errors.

Options 5 and 7 skip both filtered and manually hidden rows, like SUBTOTAL 109. Pick AGGREGATE when your amounts come from formulas that sometimes fail; otherwise SUBTOTAL is shorter and more widely understood.

Conditional totals that never depend on the filter

The sample sheet on this page cannot hide rows, so it cannot show SUBTOTAL or AGGREGATE results live. What it can show is the other way to get "the North total": state the condition in the formula. SUMIFS adds Amount where Region is North and Status is not Cancelled, the same rows you would see after filtering North and hiding the cancelled orders.

This is often the better choice for a report. A SUBTOTAL total is only right while the filter is set the way you expect, and it silently changes when someone else filters the sheet. A SUMIFS total says in the formula what it counts. Use SUBTOTAL for quick totals while you explore, and SUMIFS for numbers you send to other people.

Sum visible rows that also meet a condition

Sometimes you want both: whatever the user has filtered, plus a condition, such as only Shipped orders among the visible rows. SUMIFS cannot tell whether a row is hidden, and SUBTOTAL has no criteria. The cleanest fix is a helper column: in F2 enter =SUBTOTAL(103,A2) and fill it down. It shows 1 when the row is visible and 0 when it is hidden or filtered out.

Then add the helper as one more condition: =SUMIFS(E2:E11,D2:D11,"Shipped",F2:F11,1). Without a helper column, the one-cell formula is =SUMPRODUCT(SUBTOTAL(109,OFFSET(E2,ROW(E2:E11)-ROW(E2),0))*(D2:D11="Shipped")): OFFSET hands SUBTOTAL one cell at a time, so it returns each visible amount and 0 for hidden ones. It works, but it is hard to read and slow on long lists.