Excel SUM and SUBTOTAL: Formulas, Filters, and Examples

Formulas & functions · Updated November 18, 2026

To add a range in Excel, use =SUM(B2:B10). To add only the rows currently shown after filtering, use =SUBTOTAL(9,B2:B10); SUBTOTAL also has an option to exclude rows you hide manually.

What SUM and SUBTOTAL Mean in Excel

SUM and SUBTOTAL both calculate totals, but they respond differently to filtered or hidden data. SUM adds the values in its references regardless of whether rows are currently visible. SUBTOTAL can recalculate a total based on the visible rows in a list, which is useful when you filter a sales report, inventory list, or other dataset.

The word “subtotal” can also refer to the subtotal rows Excel inserts into grouped data. That is related to, but not quite the same as, the SUBTOTAL worksheet function. Knowing which meaning you need helps you choose the right approach: use a formula when you want a total in a cell, or use Excel’s Subtotal command when you want grouped subtotal rows and an outline you can expand or collapse.

When to Use SUM Instead

SUM is the straightforward choice for a fixed or ordinary total. It includes numbers in hidden rows, so filtering a list does not change its result. For example, an annual total often should include every month even when a filter temporarily hides some months. SUM is also useful for totals across separate ranges or individual cells.

With amounts in B2:B10, enter =SUM(B2:B10) in the cell where you want the total. If the values are 12, 18, 25, 10, 15, 20, 8, 22, and 10, the result is 140. The formula adds the whole referenced range, including any rows that a filter or manual hiding makes invisible.

A frequent mistake is using SUM at the bottom of a filtered list and expecting it to show only the visible records. It will continue to show the overall total. If the intended answer should change as the filter changes, use SUBTOTAL instead.

How the SUBTOTAL Function Works

SUBTOTAL takes a function number that specifies the calculation, followed by one or more cell references. For a sum, the function number is 9. With amounts in B2:B10, use =SUBTOTAL(9,B2:B10). The function ignores rows removed from the results by a filter, so the answer changes when you change the filter.

SUBTOTAL also ignores other SUBTOTAL formulas within its referenced range. That behavior helps prevent a grand total from adding both detail values and subtotal rows that already represent those values. It does not, however, ignore ordinary SUM formulas in the range. If your range includes a SUM subtotal as well as the detail values it totals, a SUBTOTAL grand total may count those amounts twice.

The function is intended for lists and ranges of data. References to another worksheet are fine, but SUBTOTAL does not support three-dimensional references such as a range spanning the same cells on several worksheets. It also does not automatically identify which values are meaningful business records; it simply performs the selected calculation on the eligible cells in the references.

Choose the Right Function Number

For sums, the practical choice is usually between 9 and 109. Both calculate a sum and both ignore rows excluded by a filter. The difference is how they treat rows hidden manually, such as with the Hide command or a collapsed outline:

  • 9: Adds visible rows and manually hidden rows, while ignoring rows excluded by a filter.
  • 109: Adds visible rows but excludes manually hidden rows as well as filtered-out rows.

The other function numbers select calculations such as AVERAGE, COUNT, MAX, and MIN. Numbers 1 through 11 include manually hidden rows; their counterparts from 101 through 111 exclude them. Filtered-out rows are ignored in either group. For example, 1 and 101 select AVERAGE, while 2 and 102 select COUNT. For a total that must ignore manual hiding, choose 109 rather than 9; for a total that should still include manually hidden records, choose 9.

This distinction matters when a worksheet uses hidden rows to store records that are temporarily out of view. If a user changes which rows are hidden, a formula using 109 can change even though no filter changed. Pick the behavior deliberately and make it clear to other users, especially in shared reports.

Apply a Filter and Check the Total

To make a filterable list, keep one header row above the records and avoid blank rows or columns within the data. Select a cell in the list, then choose Data > Filter. Use the drop-down arrow in a column heading to select the records to show. A SUBTOTAL formula that references the amount column will then recalculate using the rows that remain visible.

For a small example, suppose B2:B6 contains 40, 25, 15, 30, and 10, with category labels in A2:A6. The all-record total is 120. If a filter leaves only the rows containing 40, 15, and 10 visible, a SUBTOTAL sum returns 65. Clearing the filter restores the total of 120. A SUM formula continues to return 120 in both cases.

When checking a result, compare it with the visible records rather than assuming that a changed number is an error. Clear the filter to confirm the full-list total, then apply a simple filter and add the displayed values by hand. This quick check can reveal a formula that references the wrong column or stops before the last row.

Use SUBTOTAL with an Excel Table

For a list that will grow, formatting the data as an Excel Table can make a total easier to maintain. Select a cell in the list and choose Insert > Table, then confirm that the data has headers if it does. Tables provide filter arrows in the header row and typically expand when you add records directly below them.

You can turn on a Table total row by selecting a cell in the Table and choosing Table Design > Total Row. In the total row, open the cell’s drop-down list and choose Sum. Excel uses a SUBTOTAL formula for that calculation so the displayed total can respond to the Table’s filters. If the Table is named Sales and its numeric column is Amount, the equivalent formula is =SUBTOTAL(109,Sales[Amount]). That example assumes an Excel Table named Sales with a column headed Amount; the 109 setting excludes manually hidden rows too.

A Table total row is convenient for interactive reports, but it is not a replacement for every summary. If you need several grouped totals, a PivotTable or the Subtotal command may be more suitable. If you need a total that always includes every record regardless of filtering, use SUM or choose a Table calculation that matches that requirement.

Formula Totals Versus Excel’s Subtotal Command

The worksheet function and the ribbon command solve different tasks. A formula such as SUBTOTAL returns a calculation in a cell. Excel’s Subtotal command inserts subtotal and grand-total rows into a sorted list, usually grouping records by a chosen field. The command can also create outline controls so you can collapse details and view only summary rows.

To use the command, sort the records by the field you want to group, select a cell in the list, and choose Data > Subtotal. In the dialog, choose the field for each group, the calculation (such as Sum), and the column or columns to summarize. For example, a sales list sorted by region can show a subtotal for each region and a grand total.

The command is intended for a normal range rather than an Excel Table; convert a Table to a range before using it if the command is unavailable. It also inserts rows into the data, so check that the list is sorted by the grouping field first. If the rows are not grouped together, the output may not represent the categories as intended. For a compact summary that does not modify the source list, use a PivotTable instead.

Common Problems and How to Fix Them

  • The total does not change after filtering: Check whether the formula is SUM rather than SUBTOTAL, and confirm that the SUBTOTAL reference covers the filtered records.
  • The total seems too high: Look for a SUM subtotal or other calculated total inside the referenced range. SUBTOTAL ignores nested SUBTOTAL formulas, not ordinary SUM formulas, so avoid including both detail and duplicate totals.
  • Manually hidden rows behave unexpectedly: Check whether the formula uses 9 or 109. Filtered rows are excluded by both, but manually hidden rows are excluded only by the 101–111 series.
  • New records are missing: Check the end of the formula’s range. An Excel Table can expand with added records, whereas a formula with a fixed reference may need to be extended.
  • The answer is zero or too small: Confirm that the referenced cells contain numeric values. Numbers stored as text are not added as numeric values by these functions. Also check for a mistyped column reference or unexpected blanks in the data.

Errors in referenced cells are another possible cause of an error result. SUBTOTAL is not an error-cleaning function: if the eligible range contains an error value, the calculation may return an error too. Find and correct the source error, or use a separate error-handling approach appropriate to the report rather than assuming SUBTOTAL will skip it.

Frequently Asked Questions

Does SUBTOTAL ignore filtered rows?
Yes. SUBTOTAL excludes rows hidden by a filter for the ranges it calculates. The total updates when you change which records the filter displays.

What is the difference between 9 and 109 in SUBTOTAL?
Both codes calculate a sum and ignore filtered-out rows. Code 9 includes manually hidden rows, while 109 excludes them too. Use the one that matches how hidden records should be treated in your report.

Can I use SUM and SUBTOTAL in the same worksheet?
Yes. Use SUM for totals that should always include the full referenced range, and SUBTOTAL for totals that should respond to filters. Keep each formula’s purpose clear so readers do not mistake a filtered total for an all-record total.

Does SUBTOTAL ignore other subtotal formulas?
It ignores nested SUBTOTAL formulas in its reference, which helps with grouped totals. It does not automatically ignore a total made with SUM, so including ordinary subtotal rows alongside their detail values can cause double-counting.

Should I use the Subtotal command or the SUBTOTAL formula?
Use the formula when you need a total in a particular cell, especially one that responds to filtering. Use Data > Subtotal when you want Excel to insert grouped subtotal rows and outline controls in a sorted list. Use a PivotTable when you want a flexible summary without inserting subtotal rows into the source data.

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.