Excel Subtotal Filtered Rows: Formula, Steps, and Fixes

Formulas & functions · Updated November 18, 2026

Yes. In Excel for Microsoft 365 on Windows, use the SUBTOTAL function to calculate from rows that remain visible after filtering. With sales in B2:B7, =SUBTOTAL(109,B2:B7) sums the visible sales and also excludes rows hidden manually; use function number 9 instead if manually hidden rows should still count.

How SUBTOTAL works with filters

An Excel filter does not delete records. It hides rows that do not match the selected criteria, leaving matching rows visible in the worksheet. A regular SUM includes filtered-out records in its total, but SUBTOTAL is designed to calculate over visible data when a filter is applied. That makes it useful for reports where the displayed total should change as someone chooses a region, date, product, or other field.

The function takes a function number and one or more references. The number identifies the calculation, such as sum or average. For a sum, the two function numbers most people need are 9 and 109. Filtered-out rows are excluded by either one. The difference concerns rows hidden manually, for example by selecting row numbers, right-clicking, and choosing Hide: 9 includes those rows, while 109 excludes them.

When the filter changes, Excel recalculates the subtotal to reflect the records that are currently visible. The formula itself does not need to be edited for each filter choice. This is different from typing a formula that adds selected cell addresses one by one, which is difficult to maintain as criteria or data change.

Choose the right function number

Use the 1–11 family when you want manually hidden rows included, and the 101–111 family when you want manually hidden rows excluded. In both families, rows removed from view by a filter are excluded. For a sum, that means 9 includes manually hidden rows and 109 does not. The other paired choices cover calculations such as average, count, maximum, minimum, and product.

  • 9: Sum visible rows, including rows hidden manually.
  • 109: Sum visible rows, excluding rows hidden manually.
  • 1 or 101: Average, with the same distinction about manually hidden rows.
  • 2 or 102: Count numeric values, with the same distinction about manually hidden rows.

These numbers are not interchangeable with the worksheet row number or a column number. The first argument of SUBTOTAL is specifically the calculation code. If the goal is simply a filter-responsive total, choose 9 or 109 based on how manual hiding should behave.

Worked example with filtered sales

Suppose A1:B7 contains a header row and six records: Region in A1, Sales in B1; East and 100 in row 2; West and 80 in row 3; East and 150 in row 4; North and 70 in row 5; West and 120 in row 6; and East and 90 in row 7. The full sales total is 610. If a filter on Region displays only East, the visible amounts are 100, 150, and 90, for a total of 340.

With the sales values in B2:B7, enter =SUBTOTAL(109,B2:B7) in a cell outside the filtered list. The result is 610 before filtering, then 340 when only East is displayed. Filtering to West changes the result to 200, and filtering to North changes it to 70. Clearing the filter returns the result to 610. These results are the sum of the visible values, not an estimate or a count of visible rows.

Keep the summary cell outside the rows that the filter might hide. If it sits in the filtered list, it could disappear along with a record, making the result hard to find. It is also clearer to reference the value cells only, as in B2:B7, rather than including the header or the summary itself.

Apply a filter and add the subtotal

  1. Arrange the records in a continuous range with one header row and consistent columns. Remove blank rows or columns that split the list if Excel does not detect the whole range.
  2. Click a cell in the list, then select Data > Filter. Excel adds a dropdown to each header. If the data is already an Excel table, its header dropdowns may already be available.
  3. Open a header dropdown and select the criteria to display, such as East in the Region column. The other records are hidden from view, not removed.
  4. Choose a blank cell outside the filtered records for the result. With the sales in B2:B7, enter =SUBTOTAL(109,B2:B7) and press Enter. The result changes when the filter selection changes.
  5. To return to all records, clear the criterion in the relevant header dropdown. The subtotal then reflects all rows in its reference.

You can type the formula directly or select a cell and use the formula bar. The core behavior is the same in Excel for Mac; ribbon placement and appearance can vary slightly by release. In Excel for the web, filters and SUBTOTAL are also available, although some ribbon details differ from the Windows desktop app.

Using SUBTOTAL in an Excel table

For data that will grow, converting the range to a table can make filtering and maintenance easier. Select a cell in the data and choose Insert > Table, then confirm that the range has headers if it does. Tables add filter controls to their header row and normally expand when new records are entered immediately below them.

A table can also display a total row. Select a cell in the table, open the contextual table tab, and turn on Total Row. In the total row, use the dropdown in the desired column to choose a calculation such as Sum. Excel uses a filter-aware subtotal calculation for table totals, so the displayed total responds to filtering. This can be more convenient than maintaining a separate formula, especially for a compact list.

Whether using a separate formula or a table total, check what range is being summarized. A fixed reference such as B2:B7 will not automatically include records added below row 7. A table total is designed to follow the table as it expands; if you keep a standalone range formula, extend its reference when the data grows or use a table-based approach.

Common problems and how to fix them

  • The total does not change after filtering: Confirm that the formula is SUBTOTAL, not SUM, and that its reference includes the rows controlled by the filter. If calculation is set to manual, choose Formulas > Calculation Options > Automatic.
  • The result includes a manually hidden row: Replace function number 9 with 109 if manually hidden rows should be excluded too. Filtering already excludes nonmatching rows with either code.
  • The total is zero or unexpectedly small: Check that the referenced cells contain numeric values rather than numbers stored as text, and verify that the range points to the intended value column.
  • The formula is missing new records: Expand its reference or use an Excel table whose total follows added records. A fixed range does not grow just because more data is typed beneath it.
  • The summary disappears when filtering: Move it outside the filtered data range. A formula can calculate correctly and still be hidden if its own row is among the records being filtered.
  • A manual hide seems to behave differently from a filter: That is expected. Filtered-out rows are always excluded; function numbers 9–11 include manually hidden rows, while 101–111 exclude them.

Also check for multiple filters. A row must satisfy all active column criteria to remain visible, so the subtotal can fall to zero if no records meet the combined selections. Clear or broaden the criteria to confirm whether the result is responding to the visible set as intended.

SUBTOTAL compared with SUM and other options

SUM is appropriate when the desired result is the total of every cell in a range regardless of which records are filtered out. SUBTOTAL is the better choice when the total should follow the currently visible rows. The distinction matters in interactive reports: a user filtering for one region usually expects its subtotal, not the total for all regions.

SUBTOTAL also ignores other SUBTOTAL formulas within the reference range. This helps prevent a grand total from adding intermediate subtotals again when data is organized into groups. Still, avoid including unrelated totals or notes in the reference; a clearly defined range makes the calculation easier to audit.

For analyses that need multiple independently calculated groups, a PivotTable may be more suitable. PivotTables summarize fields by categories and provide interactive filtering, while a worksheet subtotal is a formula tied to a cell range and the current filtered view. A PivotTable can be easier to rearrange for exploration; SUBTOTAL is often simpler when the records are already laid out and the reader just needs a visible-row total.

Frequently Asked Questions

Does SUBTOTAL ignore filtered rows automatically?
Yes. Rows excluded by an Excel filter are omitted from SUBTOTAL calculations whether the function number is 9 or 109.

What is the difference between SUBTOTAL 9 and 109?
Both sum visible rows and ignore records filtered out. Function number 9 includes rows hidden manually, while 109 excludes manually hidden rows as well.

Does SUM ignore filtered rows?
No. A normal SUM includes values in rows hidden by a filter. Use SUBTOTAL when the result should change with the visible records.

Can I use SUBTOTAL with more than one filter?
Yes. Excel applies all active filter criteria to the list, and the subtotal calculates from the rows that remain visible after those criteria are applied.

Will the subtotal update when I add new rows?
Only if the new records are included in the formula reference. An Excel table expands more conveniently as records are added, while a fixed cell range may need to be extended.

Why does my subtotal not match the rows I can see?
Check the referenced range, whether values are stored as numbers, whether rows are manually hidden, and whether another filter is active. Also confirm that the selected function number matches how you want manually hidden rows treated.

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.