SUBTOTAL vs SUM in Excel: Totals That Respect Filters

The short answer to SUBTOTAL vs SUM: SUM adds every number in a range, while SUBTOTAL adds only the rows that are visible after a filter and skips other subtotals inside the range. On a plain list they give the same answer. The difference appears as soon as someone filters the data or adds group totals. This comparison is for anyone who builds reports that other people filter, or who has seen a grand total come out exactly double.

SUBTOTAL vs SUM at a glance#

SUM is simple and counts everything; SUBTOTAL is aware of filters, hidden rows and other subtotals.

How SUM works#

SUM adds every number you give it: ranges, single cells or typed numbers.

=SUM(C2:C7)
=SUM(C2:C7,E2:E7)
=SUM(C2,C5,100)

It ignores text and empty cells but includes everything else, even rows you can't see. That makes it predictable and fast, and it is the right choice for most fixed totals. The SUM function guide covers every form, and the article on the SUM function has more everyday examples, including totals that span several sheets.

How SUBTOTAL works#

SUBTOTAL takes a function number that says which calculation to do, then one or more ranges.

=SUBTOTAL(9,C2:C7)
=SUBTOTAL(109,C2:C7)

The most common function numbers are:

Every SUBTOTAL, whatever the number, skips rows hidden by a filter and ignores other SUBTOTAL formulas in its range. The SUBTOTAL function guide and the article on the SUBTOTAL function list all eleven calculations.

When SUM and SUBTOTAL both fail: errors#

Both functions stop at an error. If one cell in the range shows #N/A or #DIV/0!, =SUM(C2:C7) and =SUBTOTAL(9,C2:C7) both return that error. In Excel 2010 and later, AGGREGATE fixes this. It works like SUBTOTAL with an extra options argument:

=AGGREGATE(9,6,C2:C7)

Here 9 means sum and 6 means "ignore error values", so the formula adds every number and skips the broken cells. Option 7 ignores both hidden rows and errors. AGGREGATE is worth knowing for messy imported data, but SUBTOTAL remains the more common choice in reports because it is shorter and older files use it everywhere.

The same data with SUM and SUBTOTAL#

Here is a sales list with six rows, totalling 700:

Test 1: no filter#

=SUM(C2:C7) and =SUBTOTAL(9,C2:C7) both return 700. With nothing hidden, they are identical.

Test 2: filter to North#

Turn on a filter (Data > Filter, or Ctrl+Shift+L on Windows and ⌘⇧F on a Mac) and show only North. Rows 4, 5 and 6 disappear.

• =SUM(C2:C7) still returns 700.
• =SUBTOTAL(9,C2:C7) returns 330, the total of the three North rows on screen.

Anyone reading the filtered report would expect 330. The SUM total no longer matches what they see. The filter lesson shows how filters hide rows, and how to filter and sum builds a full filtered report.

Test 3: hide a row by hand#

Clear the filter, then right-click row 4 and choose Hide.

• =SUBTOTAL(9,C2:C7) returns 700: 9 ignores filters, not manual hiding.
• =SUBTOTAL(109,C2:C7) returns 500: 109 also skips the hidden row.

Test 4: group totals inside the range#

Now picture a report with a subtotal under each region, as in the exercise on this page: North rows of 100 and 150 with =SUBTOTAL(9,C2:C3) below them, and South rows of 200 and 50 with =SUBTOTAL(9,C5:C6) below them. A grand total of =SUM(C2:C7) returns 1,000, because it adds the four sales and both group totals. =SUBTOTAL(9,C2:C7) returns 500, the correct grand total, because it skips the other SUBTOTAL cells.

This is how Excel's own Data > Subtotal command works: it inserts SUBTOTAL formulas so the grand total stays correct.

When to use SUM#

Use SUM when the total should always include every row. It fits:

• Fixed totals that must not change when someone filters, such as a budget total or an invoice total.
• Adding single cells or typed numbers, such as =SUM(B2,D2,F2).
• Totals across sheets, such as =SUM(Jan:Dec!C10). See summing across sheets.
• Simple lists that no one will filter or group, where SUM is the shortest and clearest choice.

The SUM lesson practises these basics.

When to use SUBTOTAL#

Use SUBTOTAL when the total should follow what is on screen. It fits:

• Lists people filter, such as a sales log where managers pick a region.
• Reports with group totals and a grand total in the same column.
• Counting visible rows, such as =SUBTOTAL(3,A2:A100) to show how many records match the filter.
• Excel tables. The Total Row of a table uses SUBTOTAL with 109, so totals update with the filter automatically.
• Hiding rows by hand for a quick what-if: use 109 so the total ignores them.

When you use AutoSum (Alt+= on Windows, ⌘⇧T on a Mac) directly below a filtered list, Excel inserts SUBTOTAL(9, ...) instead of SUM for you. The guide to summing visible cells only covers more methods.

Common mistakes with SUBTOTAL and SUM#

1. Using SUM on a filtered report. The total shows all rows, not what the reader sees. Use SUBTOTAL.
2. Double counting group totals. A SUM grand total over a column with subtotals adds them twice. Use SUBTOTAL for both the group totals and the grand total.
3. Using 9 when you hide rows by hand. 9 only skips filtered rows. Use 109 to skip manually hidden rows too.
4. Expecting SUBTOTAL to skip hidden columns. It works down columns only. Hidden columns in a horizontal total are still added.
5. Mixing SUM and SUBTOTAL for group totals. If group totals use SUM, a SUBTOTAL grand total will include them, because it only skips other SUBTOTAL cells.
6. Forgetting the function number. =SUBTOTAL(C2:C7) is not valid. The function number always comes first.

Our recommendation#

Use SUM for totals that should always include every row, and for adding individual cells. Use SUBTOTAL, usually with 109, for any list that people will filter or hide rows in, and for any column that mixes group totals with a grand total. If you aren't sure whether a report will be filtered, SUBTOTAL is the safer choice: it gives the same answer as SUM until a filter appears, then keeps matching the screen.

In short: SUM for fixed totals, SUBTOTAL for anything that gets filtered or grouped.

Practise it#

Try the exercise on this page: build a grand total that skips the group subtotals. Then practise filters in a live spreadsheet and review the SUM function lesson: you type each formula, and it checks your answer instantly.