SUBTOTAL in Excel: Totals That Skip Subtotals and Filters

=SUBTOTAL(9,C2:C8) adds the numbers in C2:C8, like SUM, with two differences: it skips any other SUBTOTAL formula inside the range, and it skips rows hidden by a filter. The first argument, 9, says which calculation to do.

FormulasReset

C8fx

=SUBTOTAL(9,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Check	Result
2	North	Apple	120		SUM of C2:C7	890
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South total		245
8	Grand total		445

Click a cell to see its formula. Change a number or a formula and the sheet recalculates.

The grand total in C8 runs over the whole column, the subtotal rows included, and still shows 445: SUBTOTAL leaves out C4 and C7 because they hold SUBTOTAL formulas. F2 does the same with SUM and shows 890, every sale counted twice. With SUBTOTAL on every total row you can add or move groups without rewriting the grand total.

SUBTOTAL function numbers

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

When you type =SUBTOTAL(, Excel shows this list, so you do not need to memorize it. 9 and 109 (SUM), 1 (AVERAGE) and 103 (count visible rows) are the ones used most.

FormulasReset

F2fx

=SUBTOTAL(1,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Calculation	Result
2	North	Apple	120		AVERAGE (1)	88.33
3	North	Pear	80		COUNTA (3)	6
4	South	Apple	200		MAX (4)	200
5	South	Pear	45		MIN (5)	30
6	East	Apple	55		Visible rows (103)	6
7	East	Plum	30		SUM (109)	530

Click a cell to see its formula. Change a number or a formula and the sheet recalculates.

Nothing is hidden here, so each line equals the ordinary function: an average of 88.33, 6 rows, a MAX of 200, a MIN of 30 and a SUM of 530. The difference shows only when rows are hidden, which is what the next section is about.

SUBTOTAL 9 vs 109, and filtered rows

Turn on a filter with Data > Filter (Ctrl+Shift+L, Cmd+Shift+F on a Mac), then pick North in the Region drop-down. Rows of other regions are hidden:

• =SUM(C2:C7) still adds all six rows.
• =SUBTOTAL(9,C2:C7) and =SUBTOTAL(109,C2:C7) add only the visible North rows.
• =SUBTOTAL(103,A2:A7) counts the rows left on screen: 2, the same number as "2 of 6 records found" in the status bar.

The two families differ only for rows you hide by hand (select rows, right-click > Hide). 9 still adds those; 109 does not. If the total should always match what is on screen, use 109. If you hide rows only to tidy the view and still want them in the total, use 9.

SUBTOTAL works on rows only. Hidden columns are always included, so =SUBTOTAL(109,B2:G2) across a row adds hidden columns too.

The quickest way to get a SUBTOTAL is the AutoSum button with a filter on: Excel writes =SUBTOTAL(9,...) instead of SUM. Data > Subtotal goes further: on a list sorted by a column, it inserts a total row under each group and a grand total, all with SUBTOTAL, plus outline buttons to collapse the groups.

AGGREGATE: SUBTOTAL that can skip errors

If one cell in the range holds an error, SUM and SUBTOTAL return that error. AGGREGATE (Excel 2010 and later) is SUBTOTAL with an extra options argument; option 6 ignores error values.

FormulasReset

F3fx

=AGGREGATE(9,6,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Formula	Result
2	North	Apple	120		SUBTOTAL	#N/A
3	North	Pear	#N/A		AGGREGATE, ignore errors	450
4	South	Apple	200		AGGREGATE, MAX	200
5	South	Pear	45
6	East	Apple	55
7	East	Plum	30

Click a cell to see its formula. Change a number or a formula and the sheet recalculates.

C3 holds #N/A, so F2 shows #N/A too. F3 ignores it and adds the other five: 450. Its first argument uses the same numbers as SUBTOTAL (9 is SUM, 4 is MAX). Other options: 5 ignores hidden rows, 7 ignores hidden rows and errors, 3 ignores hidden rows, errors and nested SUBTOTAL and AGGREGATE formulas. Replace C3 with a number and F2 shows the same total as F3.

Practice: a grand total over subtotals

FormulasReset

C9fx

	A	B	C
1	Region	Item	Sales
2	North	Apple	120
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South	Plum	60
8	South total		305
9	Grand total

Click a cell to see its formula. Change a number or a formula and the sheet recalculates.

Your turn: The list has a subtotal under each region. Put a grand total in C9 that covers C2:C8 without counting the subtotal rows twice.

CheckShow answer

Why a SUBTOTAL total is still wrong

• The group totals use SUM. SUBTOTAL skips other SUBTOTAL formulas inside its range, not SUM formulas. A group total written as =SUM(C2:C3) is counted again. Change every total row to SUBTOTAL.
• The rows were hidden by hand and the function number is 9. Use 109.
• The data is in columns, not rows. Hidden columns are never skipped.
• You need a condition, not a filter. SUBTOTAL follows what the filter hides. To total North without filtering, use SUMIF. For a summary of every group at once, a pivot table does it without total rows in the data.