Excel SUBTOTAL Function: Codes and Examples

Formulas & functions · Updated November 22, 2026

Excel’s SUBTOTAL function calculates a chosen summary—such as a sum, average, count, minimum, or maximum—and automatically ignores rows removed by a filter. Its function number also lets you choose whether manually hidden rows are included, making it useful for summaries that should respond to filtered lists.

What the SUBTOTAL Function Does

SUBTOTAL is one Excel function with several possible calculations. You supply a number that identifies the calculation, followed by one or more cell references. For instance, the number 9 means sum, while 1 means average. This design lets the same function work as a total, average, count, or other summary without changing its basic structure.

Its best-known feature is its response to filters. If you filter a list to show only certain records, a subtotal can calculate from the visible records rather than continuing to include the filtered-out ones. That differs from a basic calculation such as SUM, which ordinarily includes every cell in its referenced range regardless of whether a filter hides its row.

The function is intended for summary calculations over lists and ranges. It does not create a subtotal row, group records, or apply a filter by itself. You choose the rows to summarize and use Excel’s filtering tools separately.

Syntax and Arguments

The function takes a function number and at least one reference. You can supply additional references when the cells to summarize are in separate ranges. For most everyday uses, the reference is a single contiguous column or range.

The function number determines both the calculation and, in the 100-series form, whether manually hidden rows are omitted. Use numbers 1 through 11 to include manually hidden rows; use 101 through 111 to exclude them. Filtered-out rows are excluded in either series.

  • Function number: identifies the summary calculation and hidden-row behavior.
  • Reference: the cell or range to calculate. The function is designed for references, rather than values typed directly as separate arguments.
  • Additional references: optional other cells or ranges to include in the calculation.

If a referenced range contains another SUBTOTAL formula, Excel ignores that nested subtotal. This helps prevent double counting when a list already has intermediate subtotal formulas and a further summary covers a larger range.

Function Number Codes

These are the 11 calculations available through the function-number argument. Add 100 to a code to use its version that excludes manually hidden rows. For example, 9 selects SUM and 109 selects SUM while excluding manually hidden rows.

  • 1: AVERAGE
  • 2: COUNT, which counts cells containing numbers
  • 3: COUNTA, which counts nonempty cells
  • 4: MAX, the largest value
  • 5: MIN, the smallest value
  • 6: PRODUCT, the product of numeric values
  • 7: STDEV, the sample standard deviation
  • 8: STDEVP, the population standard deviation
  • 9: SUM
  • 10: VAR, the sample variance
  • 11: VARP, the population variance

Excel’s current statistical function names include STDEV.S, STDEV.P, VAR.S, and VAR.P. The corresponding SUBTOTAL codes use the traditional labels shown above: codes 7 and 10 calculate sample statistics, while 8 and 11 calculate population statistics. Choose sample or population based on what your data represents, not simply on which code is familiar.

How Filtering and Hidden Rows Affect Results

Filtering and manually hiding rows are different actions. A filter hides records that do not meet its criteria; manually hiding a row changes its visibility without filtering the list. SUBTOTAL excludes filter-hidden rows for all codes from 1 to 11 and 101 to 111. The 100-series codes additionally exclude rows hidden manually. The 1–11 codes include those manually hidden rows.

For example, if you filter a sales list to show one region, a subtotal of the sales column updates to the visible region’s total. If you then clear the filter, it calculates across the records shown again. A manually hidden row is treated differently: a code such as 9 still includes it, whereas code 109 does not.

The hidden-row option applies to rows, not hidden columns. Hiding a column does not make its values disappear from a subtotal when that column is part of a referenced range. Also, hiding rows using outline controls is a form of manual hiding for this distinction; use a 100-series code if those rows should be excluded.

Examples with Cell Layouts

Suppose A1 contains a heading and cells A2:A6 contain the sales values 10, 20, 30, 40, and 50. With all rows visible and no filter applied, this formula returns 150:

With sales values in A2:A6, =SUBTOTAL(9,A2:A6) returns 150.

If you filter out the row containing 30, the same formula returns 120 because the filtered record is excluded. The calculation changes as the visible records change, even though the formula itself stays the same.

Now suppose you manually hide row 4, which contains 30, without applying a filter. With the same values in A2:A6, the 1–11 version includes that row, while the 101–111 version does not:

With sales values in A2:A6 and row 4 manually hidden, =SUBTOTAL(9,A2:A6) returns 150; =SUBTOTAL(109,A2:A6) returns 120.

To count numeric entries in a filtered list, use code 2; to count nonempty entries, use code 3. With values in B2:B6 and a filter that leaves three numeric records visible, =SUBTOTAL(2,B2:B6) returns 3. If a cell contains a formula that displays an empty string, it is still a nonempty cell for COUNTA purposes, so code 3 can count it. Check what the cells contain if a nonempty count seems higher than expected.

Using SUBTOTAL in Excel Tables

An Excel table’s Total Row is a convenient place for a filtered summary. When a table has a Total Row, Excel can offer calculations such as Sum, Average, or Count for a column; the generated total uses SUBTOTAL so it can update as you filter the table. This is useful when the list grows, because table references generally expand as new rows are added.

You can also enter a SUBTOTAL formula outside the table and refer to a table column. A table’s Total Row is not required, however: the function works with ordinary worksheet ranges too. If you build the formula yourself, check the selected range and code, especially when the table contains manually hidden rows as well as filtered records.

A table total is a summary, not a replacement for the filter itself. The filter determines which records are visible; the subtotal responds to that visibility. If the displayed result surprises you, clear the filter temporarily and check whether the Total Row updates to the total for all records.

SUBTOTAL Compared with SUM and Other Options

Use SUM when you want a straightforward total that includes every value in the specified range, even when some rows are filtered out. Use SUBTOTAL when the answer should reflect the currently visible records. For a report that users will filter, that difference is often more important than the small difference in formula length.

AGGREGATE is another Excel function for summary calculations. It offers options for ignoring certain values, including errors and hidden rows, and supports calculations such as LARGE and SMALL that are not among SUBTOTAL’s codes. It has different arguments and behavior, so it is not a drop-in rewrite for every subtotal. For a filtered sum, count, or average, SUBTOTAL is often the simpler choice.

A common misconception is that SUBTOTAL always ignores every hidden row. It always omits rows hidden by a filter, but manually hidden rows are included by codes 1–11. Use the matching 101–111 code when manually hidden rows should also be left out.

Common Problems and Checks

  • Wrong hidden-row code: If a manually hidden row is still affecting a result, change the code to the corresponding 100-series code, such as 109 for a sum.
  • Wrong kind of count: Code 2 counts numeric cells; code 3 counts nonempty cells. Text entries affect the second count but not the first.
  • Unexpected total after filtering: Confirm that the filter actually hides the records you intend to exclude and that the formula’s range covers the relevant data rows.
  • Double counting: Nested SUBTOTAL formulas are ignored, but ordinary formulas or manually typed totals are not automatically recognized as subtotals.
  • Hidden column confusion: These codes control hidden rows, not hidden columns.

For reliable results, keep the summary formula outside the range it summarizes when practical. This makes the layout easier to inspect and reduces the risk of confusing the summary row with the records being summarized.

Frequently Asked Questions

What does SUBTOTAL do in Excel?
It calculates a selected summary, such as a sum, average, or count, from referenced cells. It automatically excludes rows hidden by a filter, so the result can change when the filter changes.

What is the difference between 9 and 109 in SUBTOTAL?
Both codes calculate a sum and exclude rows hidden by filters. Code 9 includes manually hidden rows, while code 109 excludes them as well.

Does SUBTOTAL ignore filtered rows?
Yes. All supported function numbers omit rows hidden by a filter. The distinction between the two code series applies to rows hidden manually.

Can SUBTOTAL calculate an average or count?
Yes. Code 1 calculates an average, code 2 counts numeric cells, and code 3 counts nonempty cells. The other codes select additional summaries such as minimum, maximum, and standard deviation.

Why does my SUBTOTAL result change when I filter?
That is the function’s intended behavior: records hidden by the filter are excluded from its calculation. Clear the filter to see the subtotal calculated across the records that are visible again.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.