Excel SUBTOTAL Function

last modified October 5, 2026

The SUBTOTAL function is a versatile Excel function that performs calculations such as SUM, AVERAGE, and COUNT on a range while automatically ignoring rows that have been filtered out and, optionally, rows that were hidden manually. This tutorial provides a comprehensive guide to using the SUBTOTAL function with detailed examples. You'll learn basic syntax, practical applications, and advanced techniques to master this essential Excel function.

SUBTOTAL Function Basics

The SUBTOTAL function performs one of eleven calculations on a range of data while respecting hidden and filtered rows. The operation is selected through the function_num argument. The syntax is flexible and powerful.

Component	Description
Function Name	SUBTOTAL
Syntax	=SUBTOTAL(function_num, ref1, [ref2], ...)
Arguments	function_num (1-11 or 101-111), ref1 (required), ref2 (optional)
Return Value	Result of the specified calculation on the range

This table breaks down the essential components of the SUBTOTAL function. It shows the function name, basic syntax format, argument details, and return value characteristics.

Argument	Meaning
function_num	Required. A number from 1-11 or 101-111 that specifies which function to use for the subtotal. Constants 1-11 include manually hidden rows, while 101-111 exclude them.
ref1	Required. The first named range or reference for which you want the subtotal.
ref2, ...	Optional. Additional named ranges or references, from 2 up to 254 in total.

Two rules govern how SUBTOTAL treats hidden data. First, rows that are not included in the result of a filter are ignored no matter which function_num value you use. Second, rows hidden with the Hide Rows command are included with constants 1-11 and excluded with constants 101-111. SUBTOTAL is also designed for columns of data, or vertical ranges; it reacts to hidden rows, not to hidden columns.

Basic SUBTOTAL Example (Sum)

This example demonstrates the simplest use of SUBTOTAL to sum a range of values.

A	B
10
20
30
	=SUBTOTAL(9, A1:A3)

The table shows a simple spreadsheet with values in column A and a SUBTOTAL formula in cell B4 that sums the values from A1 to A3.

Basic SUBTOTAL sum formula

=SUBTOTAL(9, A1:A3)

This formula sums values in cells A1 through A3 using function_num 9 (SUM). The result will be 60 (10+20+30). This shows the basic summing capability of SUBTOTAL.

SUBTOTAL Function Numbers Reference

The function_num argument accepts 22 constants: each of 11 functions comes in two versions. Constants 1-11 include manually hidden rows, while their 101-111 counterparts exclude them. Filtered-out rows are excluded in both versions.

Function_num (includes manually hidden rows)	Function_num (ignores manually hidden rows)	Function
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

This reference table lists all 22 function_num constants. The statistical names STDEV, STDEVP, VAR, and VARP are the legacy versions of STDEV.S, STDEV.P, VAR.S, and VAR.P. Since filtered-out rows are always excluded regardless of the constant, the choice between 1-11 and 101-111 matters only when rows have been hidden manually.

SUBTOTAL 9 vs 109: Hidden Rows

The difference between 9 and 109 appears only with manually hidden rows. This example shows both constants on the same data.

A	B
10
20	(hidden row)
30
	=SUBTOTAL(9, A1:A3)
	=SUBTOTAL(109, A1:A3)

This table demonstrates how the two SUM constants treat a manually hidden row. Row 2 has been hidden with the Hide Rows command.

SUBTOTAL 9 versus 109

=SUBTOTAL(9, A1:A3)    ' 60, manually hidden rows are included
=SUBTOTAL(109, A1:A3)  ' 40, manually hidden rows are excluded

The first formula sums all three values (10+20+30) and returns 60. The second formula sums only the visible cells A1 (10) and A3 (30) and returns 40. If row 2 were hidden by a filter instead of the Hide Rows command, both formulas would return 40, because filtered-out rows are excluded for every function_num.

SUBTOTAL for Average Calculation

SUBTOTAL can calculate averages using function_num 1 or 101. This example shows average calculation while ignoring a manually hidden row.

A	B
10
20	(hidden row)
30
	=SUBTOTAL(101, A1:A3)

The table shows how SUBTOTAL calculates averages while excluding a manually hidden row. Row 2 is hidden, and the formula uses 101 (AVERAGE ignoring manually hidden rows).

SUBTOTAL average calculation

=SUBTOTAL(101, A1:A3)

This formula averages A1 (10) and A3 (30), ignoring hidden A2 (20). The result is 20 ((10+30)/2). This shows SUBTOTAL's ability to perform different calculations.

SUBTOTAL for Count Operations

SUBTOTAL can count cells with numbers (function_num 2 or 102) or non-empty cells (function_num 3 or 103). This example demonstrates counting.

A	B
10
Text	(hidden row)
30
	=SUBTOTAL(102, A1:A3)
	=SUBTOTAL(103, A1:A3)

This table shows two counting operations: counting numbers and counting non-empty cells, both ignoring the manually hidden row. Row 2 is hidden and contains text.

SUBTOTAL count examples

=SUBTOTAL(102, A1:A3)  ' 2, counts numbers ignoring the hidden row
=SUBTOTAL(103, A1:A3)  ' 2, counts non-empty cells ignoring the hidden row

The first formula counts numeric cells (A1 and A3), ignoring hidden A2. Result is 2. The second counts non-empty visible cells, and hidden A2 is also ignored. Result is 2 (A1 and A3). If row 2 were visible, the results would be 2 for 102 (text is not a number) and 3 for 103 (COUNTA counts text too).

SUBTOTAL with Multiple Ranges

SUBTOTAL can handle multiple ranges just like other Excel functions. This example shows summing across non-contiguous ranges.

A	B	C
5	10
15	20
		=SUBTOTAL(9, A1:A2, B1:B2)

This table demonstrates SUBTOTAL's ability to combine different ranges in one calculation. The formula sums values from both column A and column B ranges.

SUBTOTAL with multiple ranges

=SUBTOTAL(9, A1:A2, B1:B2)

This formula sums all values in ranges A1:A2 (5+15) and B1:B2 (10+20). The result is 50. This shows SUBTOTAL's flexibility with multiple input ranges.

SUBTOTAL with Filtered Data

SUBTOTAL is particularly useful with filtered data, because it automatically ignores filtered-out rows regardless of which function_num you use. Both SUBTOTAL(9, ...) and SUBTOTAL(109, ...) return the sum of the visible rows only.

A	B	C
100	North
200	South	(filtered out)
300	North
		=SUBTOTAL(9, A1:A3)

The table shows data filtered to show only "North" region sales. The SUBTOTAL formula with 9 sums only visible (unfiltered) rows; constant 109 would give the same result.

SUBTOTAL with filtered data

=SUBTOTAL(9, A1:A3)

This formula sums only visible cells in A1:A3 (100 and 300), ignoring filtered A2 (200). The result is 400. This automatic handling makes SUBTOTAL ideal for filtered reports.

SUBTOTAL and Nested Subtotals

If the range contains other SUBTOTAL results, they are ignored to avoid double counting. This example shows a grand total placed below a subtotal.

A	B
100
200
300
=SUBTOTAL(9, A1:A3)	(subtotal, displays 600)
400
	=SUBTOTAL(9, A1:A5)

The table shows a range in which A4 already holds a subtotal of A1:A3. The formula in B6 totals A1:A5.

Nested subtotals are ignored

=SUBTOTAL(9, A1:A5)

A plain SUM(A1:A5) would return 1000 (100+200+600+400) because it counts the subtotal in A4 a second time. SUBTOTAL(9, A1:A5) skips the nested subtotal and returns 700 (100+200+300+400). This behavior is what makes the Subtotal command on the Data tab safe to use for grand totals. If you specifically want to include nested subtotal cells, use SUM instead.

SUBTOTAL in Percentage Calculation

SUBTOTAL results can be used in other calculations. This example shows calculating a percentage of subtotal.

A	B	C
100	North
200	South	(filtered out)
300	North
		=A1/SUBTOTAL(109, A1:A3)

The table demonstrates using SUBTOTAL within a larger calculation. The formula calculates what percentage the first row is of the filtered total.

SUBTOTAL in percentage calculation

=A1/SUBTOTAL(109, A1:A3)

This formula divides A1 (100) by the filtered sum of A1:A3 (400). The result is 0.25 or 25%. This shows how SUBTOTAL can be part of more complex formulas.

SUBTOTAL vs SUM vs AGGREGATE

These three functions look similar but differ in what they include or skip. SUM always counts every cell. SUBTOTAL always skips filtered-out rows, optionally skips manually hidden rows, and ignores nested subtotal results. AGGREGATE offers 19 functions and, in addition, can ignore error values.

Behavior	SUM	SUBTOTAL	AGGREGATE
Includes filtered-out rows	Yes	Never	No, with options 1, 3, 5, 7
Includes manually hidden rows	Yes	Yes with 1-11, no with 101-111	Yes with options 0, 2, 4; no with options 1, 3, 5, 7
Skips nested SUBTOTAL results	No	Yes	Yes
Can ignore error values	No	No	Yes, with options 6 or 7
Number of functions available	1	11	19

The table shows why SUBTOTAL is the standard choice for totals in filtered lists: SUM cannot react to hidden rows at all, while AGGREGATE adds error tolerance but has a more complex syntax. A common pattern is =AGGREGATE(9, 5, A1:A3), which behaves like SUBTOTAL(109, A1:A3) but can be extended with option 6 or 7 to skip error values that SUBTOTAL would pass through.

Common SUBTOTAL Use Cases

Here are some practical scenarios where SUBTOTAL shines:

Dynamic Reports: Create summary calculations that automatically adjust when filtering data
Data Analysis: Analyze visible data while ignoring filtered-out values
Dashboard Summaries: Build flexible dashboard metrics that respond to filters
Sub-Reports: Calculate totals within filtered sections of larger datasets

Tips and Best Practices

To get the most out of SUBTOTAL, consider these tips:

Use 101-111 constants when rows may be hidden manually; for filters, any constant reacts the same way
Place SUBTOTAL formulas outside the ranges they calculate to avoid unintended nesting
Combine with AutoFilter for interactive reports; Excel's Total Row in tables also inserts SUBTOTAL automatically
Use with named ranges for clearer formulas
Test formulas with both hidden and visible data to verify results

Limitations

While powerful, SUBTOTAL has some limitations to be aware of:

Excludes manually hidden rows only with 101-111 constants; with 1-11 those rows are counted
Always excludes filtered rows, so it cannot produce a total that includes all data in a filtered list
Returns the #VALUE! error if any reference is a 3-D reference spanning multiple sheets
Reacts to hidden rows, not hidden columns, so it is designed for vertical ranges
Cannot skip error values; a single #N/A or #DIV/0! in the range spoils the result

SUBTOTAL Errors and Troubleshooting

Cause	Symptom	Fix
function_num is outside 1-11 or 101-111	#VALUE! error	Use a valid constant, for example 9 or 109
Reference spans several sheets, such as =SUBTOTAL(9, Sheet1:Sheet3!A1)	#VALUE! error	Calculate each sheet with its own SUBTOTAL and add the results
Range contains an error value	The same error is returned	SUBTOTAL cannot skip errors; fix the source or use AGGREGATE with option 6 or 7
Average-type operation with no visible numeric cells	#DIV/0! error	Make sure at least one numeric cell is visible
Horizontal range with hidden columns	Hidden columns are still counted, even with 101-111	SUBTOTAL reacts to hidden rows only; lay out data in vertical ranges
Nested SUBTOTAL formulas inside the range	Nested results are skipped, not added	This is by design to avoid double counting; use SUM to include them

The SUBTOTAL function is an essential tool for dynamic reports and filtered data analysis in Excel. Its core value is that it recalculates on the visible data: filtered-out rows are never counted, manually hidden rows follow the 1-11 versus 101-111 rule, and nested subtotals are skipped to avoid double counting. When a range must also tolerate error values, step up to AGGREGATE; when every cell must count, use SUM. Mastering these distinctions lets you build flexible, robust spreadsheets that update correctly every time the data is filtered, hidden, or expanded.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.

Ebooks