The SUBTOTAL function in Excel

Q: Why does Excel insert SUBTOTAL when I click AutoSum?

When the list is filtered, AutoSum writes SUBTOTAL(9,...) so the total follows the filter. A Table's Total Row uses SUBTOTAL codes like 109 for the same reason.

SUBTOTAL sums, counts or averages a range while ignoring two things a plain SUM includes: rows hidden by a filter, and other SUBTOTAL formulas. That makes it the right choice for totals under a filtered list and for grand totals in a sheet that already has group subtotals. The first argument is a code that picks the calculation, such as 9 for SUM.

Syntax

=SUBTOTAL(function_num, ref1, [ref2], ...)

function_num	Which calculation to run: `9` SUM, `1` AVERAGE, `2` COUNT, `3` COUNTA, `4` MAX, `5` MIN. Add 100 (like `109`) to also skip rows you hid by hand.
ref1	The range to calculate on, like `C2:C8`.
ref2, ...	Optional. More ranges to include.

Try it yourself

This is a live spreadsheet. Type the formula and press Enter.

Each category already has a SUBTOTAL row. In C9, use SUBTOTAL over C2:C8 to get the grand total without counting the category subtotals twice.

	A	B	C
1	Category	Item	Amount
2	Travel	Flights	1240
3	Travel	Hotel	860
4	Travel subtotal		2100
5	Office	Laptop stand	95
6	Office	Printer ink	140
7	Office	Desk chair	310
8	Office subtotal		545
9	Grand total
10
11
12

Tip: Use the function code for SUM, then the whole Amount column above the grand total.

Examples

=SUBTOTAL(9,C2:C8)→ 15400

A grand total over the whole column. The two region totals are SUBTOTAL formulas, so they're skipped and nothing is counted twice.

=SUBTOTAL(4,C2:C8)→ 4200

Code 4 is MAX. It returns the best single rep, not the North total, because subtotal rows are ignored.

=SUBTOTAL(1,C5:C7)→ 2700

Code 1 is AVERAGE: the average sale for the South reps.

Example data

1	Region	Rep	Sales
2	North	Ana	4200
3	North	Ben	3100
4	North total		7300
5	South	Chloé	2800
6	South	Dev	3900
7	South	Eun-ji	1400
8	South total		8100

Common mistakes

Using SUM under a filtered list
SUM keeps adding rows that a filter hides, so the total doesn't match what you see. Use =SUBTOTAL(9,range) to total only the visible rows.
Mixing a SUM into the subtotals
SUBTOTAL only skips other SUBTOTAL cells. If a group total is a plain SUM, the grand total counts it twice. Use SUBTOTAL for every level.
Wrong function code
SUBTOTAL(2,...) is COUNT, not SUM. Learn the common ones: 9 SUM, 1 AVERAGE, 2 COUNT, 3 COUNTA, 4 MAX, 5 MIN.
Expecting 9 to skip rows hidden by hand
Codes 1–11 still include rows you hid with Hide. Use 101–111, like 109, to skip those too. Filtered-out rows are skipped by both.

FAQ

What is the difference between SUBTOTAL 9 and 109?+

Both add only the rows a filter shows. 109 also leaves out rows you hid manually with Hide, while 9 still includes them.

Why does Excel insert SUBTOTAL when I click AutoSum?+

When the list is filtered, AutoSum writes SUBTOTAL(9,...) so the total follows the filter. A Table's Total Row uses SUBTOTAL codes like 109 for the same reason.

Can SUBTOTAL ignore errors?+

No. An error in the range makes SUBTOTAL return that error. AGGREGATE works like SUBTOTAL and has an option to skip errors.

Lesson

Master SUBTOTAL step by step: Filter a table

Turn on filter buttons with Ctrl+Shift+L, filter by one or several values and by a number condition, total and count only the visible rows with SUBTOTAL, then clear the filters.

Related functions

SUM COUNT AVERAGE COUNTA