Excel SUBTOTAL Formula for Sum: Examples and Tips

Formulas & functions · Updated November 22, 2026

To sum a range with Excel’s SUBTOTAL function, use =SUBTOTAL(9,A2:A20); it sums visible values and automatically excludes rows removed by a filter. To also exclude rows hidden manually, use =SUBTOTAL(109,A2:A20).

What the SUBTOTAL sum formula does

SUBTOTAL is an Excel function for calculating a summary, such as a sum, average, or count, from a list. Its first argument selects the calculation, and its remaining arguments identify the cells to summarize. For a sum, the function number is 9 or 109. The range can be a regular cell range, such as A2:A20, or a table column reference.

The useful distinction between SUBTOTAL and the ordinary SUM function is that SUBTOTAL responds to filtered data. If a filter hides some records, a SUBTOTAL sum recalculates using the records still displayed. SUM continues to include filtered-out values in its result. This makes SUBTOTAL particularly useful for reports where someone will filter a list by date, category, customer, or another field.

SUBTOTAL also ignores other SUBTOTAL formulas within its reference, which helps prevent a grand total from counting section subtotals a second time. This behavior is handy in lists divided into groups, but it is worth remembering when a result seems lower than a SUM over the same cells.

Understanding function numbers 9 and 109

The function number determines both the operation and how Excel treats rows hidden manually. The numbers 1 through 11 include manually hidden rows, while 101 through 111 exclude them. Both sets exclude rows removed by a filter. For summing, that gives two choices:

  • 9: Sum visible rows after filtering, but include rows hidden manually.
  • 109: Sum visible rows after filtering and omit rows hidden manually.

Thus, 9 and 109 both mean SUM; the difference is not whether the function sums, but how it treats manually hidden rows. A row hidden by a filter is excluded with either number. A row hidden using the row header’s Hide command is included with 9 and excluded with 109.

Excel also offers function numbers for other summaries, including 1 for AVERAGE, 2 for COUNT, 3 for COUNTA, 4 for MAX, and 5 for MIN. The corresponding numbers in the 100 series apply the same rule of excluding manually hidden rows. If the goal is a sum, however, use 9 or 109 rather than a number for a different calculation.

Basic setup in an Excel worksheet

For a simple list, place the values in one column and put the SUBTOTAL formula outside the list, usually below it. For example, if the amounts are in A2:A20 and the total will be in A21, enter the formula in A21. Keeping the result outside the referenced range avoids accidentally including the total in its own calculation.

  1. Arrange the data as a list with a header row and one record per row.
  2. Choose whether manually hidden rows should count. Use 9 to include them or 109 to exclude them.
  3. Select the cell where the total should appear and enter the appropriate SUBTOTAL formula.
  4. Press Enter, then apply or change filters to confirm that the total updates as expected.

On Excel for Microsoft 365 on Windows, filters can be applied from Data > Filter. Excel for Mac also has a Filter command on the Data tab, though the ribbon’s layout can differ slightly. Filtering a list does not change the formula: the same SUBTOTAL result adjusts as rows enter or leave the visible filtered results.

When the list is an Excel table, structured references can make the formula easier to read. With a table named Sales and a column named Amount, place the total outside the table and use =SUBTOTAL(9,Sales[Amount]). The table reference expands as rows are added, so the formula can include new records without manually changing the range.

Worked example with a filtered list

Suppose A2:A6 contains five amounts: 12.50, 8.25, 14.00, 5.25, and 10.00. Their complete total is 50.00. With those values in A2:A6 and the result in A7, enter =SUBTOTAL(9,A2:A6). The result is 50.00 before any filtering.

If a filter hides the row containing 8.25, the visible values total 41.75. The same formula now returns 41.75 because filtered-out rows are omitted. No change to the formula is needed. This is the central reason to use SUBTOTAL instead of SUM in a filtered report.

Now suppose the row containing 14.00 is hidden manually rather than filtered out. With 9, the result remains 50.00 because function number 9 includes manually hidden rows. With 109, the result is 36.00: the visible amounts are 12.50, 8.25, 5.25, and 10.00. These figures illustrate that filtering and manually hiding a row are not the same operation for SUBTOTAL.

In an actual sheet, the cell values need not be formatted as currency for the calculation to work. Formatting changes how numbers appear, not the arithmetic. If the cells contain text that looks like a number rather than numeric values, that text may not contribute to the sum; checking the cell contents and number formats can help diagnose an unexpected total.

How filtering and hidden rows affect the result

SUBTOTAL’s response to filters is automatic. When a filter excludes records, those rows no longer contribute to a SUBTOTAL calculation, whether the function number is 9 or 109. This makes the formula appropriate for visible-only totals in filtered lists. It does not require adding criteria for every filter selection.

Manual hiding is different. Hiding a row through its row header does not remove it with function number 9, but function number 109 does omit it. Use 109 when the intended meaning of the result is the total of every currently visible row, regardless of whether a row disappeared because of a filter or because someone hid it manually.

Hiding a column is not the same as hiding a row for this purpose. The 101–111 numbering convention controls treatment of manually hidden rows; it is not a general instruction to ignore every hidden cell in the worksheet. For a typical list arranged vertically, the relevant choice is whether manually hidden records should be included.

Also note that a filter does not delete data. A row omitted from a filtered SUBTOTAL is still present in the worksheet and may be included again when the filter changes or is cleared. This is useful for interactive summaries, but it means the displayed total represents the current visible selection rather than necessarily the entire underlying dataset.

Choosing between SUBTOTAL and SUM

Use SUBTOTAL when a total should adapt to filtered records or when a sheet contains nested subtotal calculations. Use SUM when the intended result should include every number in a range whether or not rows are filtered out. For example, a fixed all-record total may be better represented by SUM if readers should not mistake it for a visible-only total.

With amounts in B2:B100 and a desired all-record total, use =SUM(B2:B100). With the same layout and a total that should change when rows are filtered, use =SUBTOTAL(9,B2:B100). The difference matters most when users interact with filters; when no rows are filtered or hidden, the two formulas usually return the same sum, assuming the same numeric cells are included.

SUBTOTAL is not a substitute for every kind of conditional total. It does not let you specify a criterion such as a particular department or a date range inside its arguments. For totals based on explicit conditions, SUMIF or SUMIFS may be a better fit. Those functions answer a different question: which values meet specified criteria, rather than which rows remain visible after filtering.

Common formula problems and checks

If the result does not change after filtering, check that the formula really uses SUBTOTAL and that the filtered rows are inside its referenced range. Also confirm that the filter is hiding worksheet rows rather than merely changing a separate display or report. A range that stops above the last data row will not include newly added records.

If the total seems too large after hiding rows, check whether the formula uses 9. That number includes rows hidden manually. Change it to 109 if those manually hidden records should be excluded. Conversely, if a result seems unexpectedly small, verify that 109 was not chosen when manually hidden values should remain part of the sum.

Other useful checks include:

  • Confirm that the referenced cells contain numbers, not text that only looks numeric.
  • Check that the first and last rows of the range match the intended data.
  • Look for SUBTOTAL formulas inside the range; SUBTOTAL ignores nested subtotals by design.
  • Make sure the formula cell is outside the range being totaled.
  • Check whether a filter is excluding records that should be visible in the result.

If Excel shows a formula as text instead of calculating it, the cell may be formatted as Text, or the formula may have been entered with an apostrophe before the equals sign. Set the cell to General, then edit and re-enter the formula. If a formula is present but not recalculating, check that calculation is set to Automatic on the Formulas tab under Calculation Options.

Frequently Asked Questions

What is the formula for subtotal sum in Excel?
For values in A2:A20, use =SUBTOTAL(9,A2:A20). It sums the range while excluding rows hidden by a filter. Use function number 109 instead if manually hidden rows should also be excluded.

Does SUBTOTAL 9 ignore filtered rows?
Yes. Function number 9 excludes rows removed by a filter, but includes rows hidden manually. Function number 109 excludes both filtered-out rows and manually hidden rows.

What is the difference between SUBTOTAL 9 and 109?
Both calculate a sum. Number 9 includes manually hidden rows; 109 excludes manually hidden rows. Both exclude rows hidden by filters.

Can I use SUBTOTAL with an Excel table?
Yes. With a table named Sales and an Amount column, place the total outside the table and use =SUBTOTAL(9,Sales[Amount]). The structured reference follows the table column as records are added.

Why does my SUBTOTAL result not include every number?
Filtered-out rows are omitted, and SUBTOTAL ignores other SUBTOTAL formulas within its range. If you need all underlying values regardless of filtering, use SUM; also check the range and whether some entries are stored as text.

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.