SUBTOTAL Formula Usage in Excel: Filters and Examples

Formulas & functions · Updated November 22, 2026

In Excel, use the SUBTOTAL formula to calculate a sum, average, count, or other summary while automatically ignoring rows excluded by a filter. Its basic syntax is =SUBTOTAL(function_num, ref1, [ref2], ...); choose a function number such as 9 for SUM, then provide the range or ranges to summarize.

What SUBTOTAL does

SUBTOTAL is designed for summaries of lists and tables that people filter or organize into groups. Unlike a regular SUM, it recalculates when a filter hides records, so the displayed total reflects the rows currently visible. You can also choose whether it includes rows hidden manually, a useful distinction when a worksheet has both filters and rows hidden with Excel’s Hide command.

The function number determines the calculation. The reference arguments identify the cells to evaluate; they can be one range or several ranges. For example, the first reference might be a column of sales amounts, while another reference could be a separate block of amounts elsewhere in the sheet. SUBTOTAL does not summarize values typed directly into its arguments; it works on references to cells.

SUBTOTAL is also used by Excel’s Subtotal command to add grouped summaries to a sorted list. The formula and the command are related, but they are not the same thing: the formula calculates a result, while the command can insert summary rows and an outline for expanding or collapsing groups.

Function numbers and what they calculate

The first argument is a number from 1 to 11, or from 101 to 111. Each number selects a calculation. The matching pairs perform the same calculation but treat manually hidden rows differently:

  • 1 or 101: AVERAGE
  • 2 or 102: COUNT, for cells containing numbers
  • 3 or 103: COUNTA, for nonblank cells
  • 4 or 104: MAX
  • 5 or 105: MIN
  • 6 or 106: PRODUCT
  • 7 or 107: STDEV.S, the sample standard deviation
  • 8 or 108: STDEV.P, the population standard deviation
  • 9 or 109: SUM
  • 10 or 110: VAR.S, the sample variance
  • 11 or 111: VAR.P, the population variance

For instance, use 9 when you want a filtered sum, 1 when you want a filtered average, or 3 when you want to count visible nonblank cells. The 1–11 versions include manually hidden rows, while the 101–111 versions exclude them. Both sets exclude rows removed from view by a filter.

How filtered and hidden rows are handled

The filter behavior is the key reason to choose SUBTOTAL instead of an ordinary SUM or COUNT. When a filter removes records from the visible results, SUBTOTAL leaves those filtered-out records out of its calculation. Clear the filter or change its criteria, and the result updates to reflect the newly visible rows.

Manual hiding is different. If you hide a row using the row heading’s Hide command, a function number from 1 through 11 still includes that row. Choose the corresponding number from 101 through 111 to exclude it. This gives you control over whether a manually hidden record is merely out of sight or should also be omitted from the summary.

For example, a filtered list may show only one department while other departments are hidden by the filter. Either form of SUBTOTAL responds to that filter. If you then manually hide one of the department’s visible rows, a 9-based summary still counts it, but a 109-based summary does not. That distinction can explain why two apparently similar totals differ.

A worked example with a visible total

Suppose A1 contains the heading Category and B1 contains Sales. Rows 2 through 6 contain sales of 120, 80, 150, 50, and 100 in B2:B6, for a complete-list total of 500. With the sales figures in B2:B6, enter this formula in a cell outside that range:

=SUBTOTAL(9,B2:B6)

With no filter excluding records, the result is 500. If a filter leaves only the rows containing 120, 150, and 100 visible, the result becomes 370. The arithmetic is 120 + 150 + 100 = 370; the hidden-by-filter values 80 and 50 are not included.

To count the visible nonblank category entries, with categories in A2:A6, use:

=SUBTOTAL(103,A2:A6)

If three category cells are visible and nonblank, this returns 3. The 103 code also ignores rows hidden manually. If you want those manually hidden rows included in the count, use 3 instead. COUNT and COUNTA are not interchangeable: COUNT counts numeric cells, whereas COUNTA counts cells that are not blank.

How to enter and check the formula

  1. Put a clear heading above the data and keep each record on its own row. Avoid placing the result inside the range it summarizes.
  2. Select the cell where the summary should appear and type an equals sign followed by SUBTOTAL, the chosen function number, and the range.
  3. Press Enter, then apply a filter and check that the total changes as expected. In Excel for Microsoft 365 on Windows, filtering can be turned on from Data > Filter.
  4. Test a manually hidden row if the distinction matters. Compare the 1–11 code with its 101–111 counterpart to confirm which behavior suits the report.

For a range in B2:B100, for example, a visible-row total uses:

=SUBTOTAL(9,B2:B100)

When the list grows, consider converting it to an Excel table using Insert > Table. Tables make it easier to keep headers and filters consistent as records are added. You can use SUBTOTAL with a suitable table column reference, or keep a regular range reference and extend it as needed. When entering a structured reference, use the exact table and column names shown in your workbook.

Excel for Mac has the same SUBTOTAL function and the same function-number behavior. Ribbon labels and layout may vary slightly with the Excel release, but the formula itself does not change. Excel for the web also supports the formula; some worksheet-management commands may differ from the Windows desktop ribbon.

Using Excel’s Subtotal command for grouped data

If the goal is to insert a subtotal after every category, rather than show one overall filtered total, use Excel’s Subtotal command. First sort the list by the column that defines the groups, such as Category. Select a cell in the list, then choose Data > Subtotal on Excel for Microsoft 365 for Windows. In the dialog, choose the grouping field, the summary function, and the column or columns to subtotal.

Excel inserts summary rows between groups and a grand total, along with outline controls that let you collapse or expand the detail. The inserted summaries use SUBTOTAL formulas, allowing them to respond to filtering. Sorting first matters: if the categories are mixed together, the command can create repeated subtotal groups instead of one summary per category.

The Subtotal command is intended for a normal range, not an Excel table. If the command is unavailable or disabled because the data is a table, convert the table to a range first if you specifically need this outline-style result. The formula itself remains useful in tables and other filtered lists. On Mac, look for the Subtotal command on the Data tab; its availability and placement can vary by Excel version.

Common mistakes and limitations

  • Choosing the wrong code: A 9 includes manually hidden rows, while 109 excludes them. Both ignore rows filtered out of view.
  • Putting the result inside its own range: Keep the summary cell outside the referenced data. SUBTOTAL ignores nested SUBTOTAL formulas in a referenced range, which helps avoid double-counting subtotals, but placing a formula within its own range is still confusing and should be avoided.
  • Expecting a filtered total to equal the full-list total: The result is intentionally based on visible records. Clear filters to see the total for the full list.
  • Using COUNT when text entries matter: COUNT counts numbers, not ordinary text labels. Use the COUNTA code when the aim is to count nonblank cells.
  • Assuming hidden columns are controlled the same way: The 101–111 codes specify how manually hidden rows are treated. They are not a general switch for every hidden-column or display behavior.

SUBTOTAL also does not make poor source data reliable. Blank cells, text stored where numbers are expected, duplicate records, or an incorrect range can still lead to a misleading summary. Check the source column and the referenced range when a result seems wrong. If a referenced cell contains an error, the resulting calculation may also show an error rather than a usable total.

SUBTOTAL compared with SUM and alternatives

Use SUM when you want every value in a range included regardless of filtering. Use SUBTOTAL when filtered-out rows should be excluded. For example, a dashboard total intended to follow a department filter is a natural use for SUBTOTAL, while a fixed control total meant to represent all records may be better served by SUM.

For criteria-based calculations, SUMIFS or COUNTIFS may be a better fit. Those functions calculate based on conditions you specify in the formula, even when rows do not happen to be visible. SUBTOTAL instead follows the current filter state. A PivotTable is another alternative when readers need to group, rearrange, and summarize a larger dataset interactively. Choose based on whether the summary should follow visible rows, explicit criteria, or a pivoted report layout.

Frequently Asked Questions

What is the basic SUBTOTAL formula in Excel?
Use =SUBTOTAL(function_num, ref1, [ref2], ...). Replace function_num with a code such as 9 for SUM, and replace ref1 with the range to calculate. Additional reference arguments are optional.

Does SUBTOTAL ignore filtered rows?
Yes. SUBTOTAL excludes rows hidden by a filter for both the 1–11 and 101–111 code sets. Its result changes when the filter criteria change.

What is the difference between SUBTOTAL 9 and 109?
Both calculate a sum and ignore filtered-out rows. Code 9 includes manually hidden rows; code 109 excludes them as well.

Can SUBTOTAL calculate an average or count?
Yes. Use 1 or 101 for AVERAGE, 2 or 102 for COUNT, and 3 or 103 for COUNTA. Pick the second code in each pair when manually hidden rows should be excluded.

Why does my SUBTOTAL result change when I filter the list?
That is expected: the function calculates over the rows currently visible after filtering. Clear the filter to include all records again, or use a different function if the result should not depend on visibility.

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.