Excel SUBTOTAL Function: Syntax, Formulas and Examples

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

The SUBTOTAL function in Excel returns a summary value for a list or database, such as a sum, average or count. Its key advantage over SUM or AVERAGE is that it can ignore rows you have hidden or filtered out, so totals stay correct when you narrow down your data [1]. You choose the calculation with a function number, and you can control whether manually hidden rows are included or excluded.

Quick Answer

SUBTOTAL takes the form =SUBTOTAL(function_num, ref1, [ref2], ...) where function_num selects the calculation [1].
Function numbers 1 to 11 include manually hidden rows, while 101 to 111 exclude them [1].
Filtered-out cells are always excluded, whichever function number you use [1].
Nested SUBTOTAL formulas inside the range are ignored, which prevents double counting [1].
The Subtotal command on the Data tab builds these formulas for you, but it is not available inside an Excel table [2].

Syntax

The SUBTOTAL function has one required function number and at least one reference.

Argument	Required?	Meaning
`function_num`	Required	A number from 1 to 11 or 101 to 111 that specifies which function to use [1]
`ref1`	Required	The first named range or reference to subtotal [1]
`ref2, ...`	Optional	Additional named ranges or references, up to 254 in total [1]

The function numbers map to standard calculations. The 1 to 11 set includes manually hidden rows, and the 101 to 111 set excludes them [1].

Function_num (includes hidden)	Function_num (ignores hidden)	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

How It Works

SUBTOTAL is a single function that behaves like eleven different functions. When you write =SUBTOTAL(9, B2:B50), Excel runs a SUM over that range. When you write =SUBTOTAL(1, B2:B50), it runs an AVERAGE. The function number is the switch.

The behavior around hidden rows is the part most people care about. Rows hidden by the Hide Rows command are included when you use 1 to 11 and excluded when you use 101 to 111 [1]. Rows removed by a filter are excluded either way, because filtered-out cells are always excluded [1]. That means a SUBTOTAL at the bottom of a filtered list updates automatically as you change the filter.

SUBTOTAL also skips other SUBTOTAL formulas inside its range. If you have subtotals for each region and then a grand total built with SUBTOTAL, the grand total ignores the regional subtotals and counts only the underlying values [1]. This is what stops a grand total from doubling the numbers it already contains.

One practical consequence is that SUBTOTAL is the natural companion to the Subtotal command. That command inserts subtotal and grand total rows using the SUBTOTAL function, and you can edit the formulas afterward [1]. The command is grayed out when your data is in an Excel table, because subtotals are not supported in tables [2]. To use it there, convert the table to a normal range first, which removes table functionality except formatting, or build a PivotTable instead [2].

Worked Example

Suppose you track monthly sales in a column and want a total that respects your filters. Your data sits in cells B2 through B50, and you want a sum.

Enter this formula in a cell below the data:

=SUBTOTAL(9, B2:B50)

This returns the sum of B2:B50. If you later hide a few rows manually, the total still includes them because 9 is in the 1 to 11 group [1]. If you switch to =SUBTOTAL(109, B2:B50), those manually hidden rows drop out of the total [1].

Now apply a filter to the list and show only one region. Both formulas recalculate to include only the visible, filtered-in rows, because filtered-out cells are always excluded [1]. The same logic applies to a count. If you want to count visible numeric entries, use =SUBTOTAL(2, B2:B50), and for a count of non-empty visible cells use =SUBTOTAL(3, B2:B50).

More Examples

Average of visible values. To average a column while ignoring manually hidden rows, use =SUBTOTAL(101, C2:C100). This is useful when you hide outliers or incomplete records and want the average to reflect only what remains.

Maximum in a filtered list. =SUBTOTAL(104, D2:D200) returns the largest visible value. The 104 number excludes manually hidden rows, and the filter exclusion applies on top of that [1].

Grand total that ignores regional subtotals. If rows 10, 20 and 30 hold regional subtotals built with SUBTOTAL, then =SUBTOTAL(9, B2:B40) ignores those three subtotal cells and sums only the detail rows [1]. This is the double-counting protection in action.

Counting non-empty cells. =SUBTOTAL(3, A2:A500) counts visible non-empty cells, which is handy for checking how many records survive a filter.

Combining with other lookup tools. If you need to pull a value from a filtered list before subtotaling, the Excel FILTER function can produce the visible set, and VLOOKUP can retrieve a matching record. For conditional totals that are not tied to hidden rows, SUMIF and SUMIFS are usually the better fit.

Errors and How to Fix Them

#VALUE! from a bad function number. If function_num is not one of the allowed values, Excel returns an error. Check that you used a whole number between 1 and 11 or 101 and 111 [1].

An error value in the range. SUBTOTAL ignores text in the reference, but if any cell in the range holds an error such as #N/A or #DIV/0!, SUBTOTAL returns that error. Fix or exclude the error cells.

Wrong total after filtering. If your total does not change when you filter, you may have used SUM instead of SUBTOTAL. Replace it with =SUBTOTAL(9, range) so filtered rows are excluded [1].

Hidden rows still counted. If manually hidden rows are appearing in your total, you are using a 1 to 11 number. Switch to the matching 101 to 111 number to exclude them [1].

Subtotal command grayed out. This happens when your data is in an Excel table, because subtotals are not supported in tables [2]. Convert the table to a normal range or use a PivotTable [2].

Common Mistakes

Using SUM when you need filter-aware totals. Fix it by switching to =SUBTOTAL(9, range), which excludes filtered-out cells [1].
Mixing up the 1 to 11 and 101 to 111 groups. Remember that 1 to 11 includes manually hidden rows and 101 to 111 excludes them [1].
Assuming SUBTOTAL ignores all hidden rows. It always ignores filtered-out cells, but manually hidden rows depend on your function number [1].
Placing a SUBTOTAL inside its own range. A formula that references the cell it sits in creates a circular reference. Keep the total outside the range it summarizes.
Expecting the Subtotal command to work in a table. It is grayed out there, so convert the table to a range or use a PivotTable [2].
Forgetting that nested SUBTOTALs are skipped. If your grand total looks low, check whether the range contains other SUBTOTAL formulas, since those are ignored by design [1].

Limitations

SUBTOTAL summarizes values, so it cannot filter, sort or reshape your data on its own. It also does not evaluate conditions the way SUMIF or COUNTIF do. If you need a total that depends on criteria such as region equals "North," SUBTOTAL alone will not do it, and you would combine filtering with SUBTOTAL or use a conditional function.

The hidden-row behavior can mislead if you are not careful. A total built with 9 includes manually hidden rows, so it can look wrong to someone who expects hidden data to be excluded. A total built with 109 excludes them, which can look wrong to someone who expects every row to count. Document which function number you used so the number is interpreted correctly. Also remember that subtotals are not supported in Excel tables, which limits how you can combine them with structured references [2].

Frequently Asked Questions

What is the difference between function numbers 9 and 109?

Both calculate a sum. The difference is how they treat manually hidden rows. Number 9 includes rows hidden with the Hide Rows command, and number 109 excludes them [1]. Filtered-out cells are excluded in both cases [1].

Does SUBTOTAL ignore filtered rows automatically?

Yes. Filtered-out cells are always excluded, no matter which function number you choose [1]. This is why SUBTOTAL is the standard choice for a total that sits under a filtered list.

How do I use the Subtotal command in Excel?

Select a cell in your data, go to the Data tab, and click Subtotal in the Outline group [2]. In the dialog box, choose the summary function you want, such as Sum, and Excel inserts the subtotal rows using the SUBTOTAL function [2]. You can edit those formulas afterward [1].

Why is the Subtotal command grayed out?

It is grayed out when your data is in an Excel table, because subtotals are not supported in tables [2]. Convert the table to a normal range to enable the command, which removes table functionality except formatting, or build a PivotTable instead [2].

Can SUBTOTAL count text values?

Yes. Use function number 3 or 103, which map to COUNTA and count non-empty cells rather than only numbers. Number 3 includes manually hidden rows and number 103 excludes them [1]. For a count of numeric entries only, use 2 or 102.

If you are building out a reporting sheet, pair SUBTOTAL with text and lookup tools such as the Excel TEXT function for formatting labels and XMATCH for locating rows. When you need to pull a specific field out of a wide table, Excel INDIRECT can turn a text address into a live reference.