Excel SUM Formula That Changes With Filters

Use =SUBTOTAL(109,B2:B100) to sum only the currently visible values in B2:B100. When you filter the rows, Excel recalculates the result using the rows that remain visible, so the total changes with the filter.

Why SUM does not change with a filter

The ordinary SUM function adds every numeric value in its referenced range, including values in rows hidden by an AutoFilter. Filtering changes which rows are displayed, not which rows are included in a conventional SUM calculation. That is why a total at the bottom of a filtered list can look too large: it may still include records you cannot see.

SUBTOTAL is designed for calculations on lists and filtered ranges. It recognizes rows excluded by a filter and omits them from the result. The first argument tells SUBTOTAL which calculation to perform; 109 means SUM while also excluding manually hidden rows. The second argument is the range to calculate.

For example, suppose B2:B6 contains sales of 120, 80, 150, 50, and 100. The unfiltered total is 500. If a filter leaves only the first, third, and fifth records visible, the subtotal is 370: 120 + 150 + 100. Clear the filter and it returns to 500.

Set up a filter-aware total

Put the total in a cell outside the records being filtered. If your headers are in row 1 and sales amounts are in B2:B100, enter this in a separate cell such as B101:

With sales amounts in B2:B100, use =SUBTOTAL(109,B2:B100). With the example values 120, 80, 150, 50, and 100 and no filter applied, the result is 500.

Select a cell in the data list and choose Data > Filter, or select the range and press Ctrl+Shift+L on Windows. On Mac, use Command+Shift+F to toggle filtering.
Use the arrow in the relevant header to choose which records to show.
Place the SUBTOTAL formula outside the filtered list, then check that it changes when you apply or clear a filter.

Keep the formula cell outside the rows that the filter can hide. If it sits within the filtered range, it may disappear when its row is excluded, and it may also become part of the data being filtered. A total below the list is convenient, but leave the total outside the actual filter range or use an Excel Table, which separates the table’s data rows from its total row.

What the 9 and 109 codes mean

SUBTOTAL accepts a function number that selects the calculation. The number 9 requests SUM. The number 109 also requests SUM, but differs when rows are hidden manually: it excludes those rows as well as rows removed by a filter. For a filter-responsive total that should ignore both kinds of hidden rows, 109 is usually the clearest choice.

Use 9 instead if manually hidden rows should still count, while filtered-out rows should not. Both codes recalculate when the filter changes. A manually hidden row is one hidden with the row commands or outline controls, rather than excluded by a filter.

The same numbering pattern applies to other common summaries. For instance, 1 and 101 select AVERAGE, while 2 and 102 select COUNT. The lower codes include manually hidden rows; the codes beginning with 100 exclude them. Filtered-out rows are excluded either way. Choose the function number that matches the question you are asking, rather than assuming every SUBTOTAL is a sum.

Use an Excel Table for growing data

An Excel Table can make the setup easier to maintain when records are added. Select a cell in the data and choose Insert > Table, or press Ctrl+T on Windows; on Mac, use Command+T. Confirm that the range has headers if it does, then use the filter arrows in the table headers. Tables expand as you add rows directly below them and carry formulas and formatting into new rows.

For a table named SalesTable with a column named Amount, enter the total outside the table using its structured column reference:

With the Amount column in a table named SalesTable, use =SUBTOTAL(109,SalesTable[Amount]). If the visible amounts are 120, 150, and 100, the result is 370.

You can also display a total row through Table Design > Total Row after selecting a table cell. The total row provides a convenient built-in summary area; choose the calculation in its dropdown for the relevant column. SUBTOTAL is the appropriate function for a table total that should respond to filtering. On Mac, the contextual tab is also called Table Design in current Microsoft 365 versions, though its position can differ by window layout. Excel for the web has table tools as well, but ribbon placement may vary.

Filtering, hiding, and other ways rows disappear

Filtering is not the same as manually hiding rows. A normal SUM includes both filtered-out rows and manually hidden rows. SUBTOTAL excludes filter-hidden rows; its 9 code still includes manually hidden rows, while 109 excludes them too. This distinction matters when a workbook uses both filters and manual row hiding.

SUBTOTAL also ignores other SUBTOTAL formulas within its reference range. That helps avoid counting intermediate subtotals again when summarizing grouped data. It does not, however, automatically ignore every kind of row that looks hidden: for example, formatting a value in the same color as the background does not remove it from the calculation.

Filtering by a table’s dropdown, a range’s AutoFilter, or a slicer connected to a table changes which records are visible; the SUBTOTAL result updates accordingly. A formula that uses SUMIFS instead calculates based on stated criteria, not on the current visible state. A worksheet’s manually hidden rows and filter state are separate controls, so decide which behavior the result needs before choosing the function.

SUBTOTAL compared with SUM and SUMIFS

SUM is suitable for a fixed total that should include all values in the referenced range regardless of filtering. It is simple, but is not a visible-rows total.
SUBTOTAL is the direct choice for a result that should follow the visible rows in a filtered list. It can also exclude manually hidden rows when you use the 100-series code.
SUMIFS is useful when the total should follow one or more explicit conditions, such as a particular region or date range. It does not automatically treat the current filter as a condition.

These functions answer different questions. If the request is “What is the sum of the rows I can currently see?”, use SUBTOTAL. If it is “What is the sum for the East region, whether or not those records are visible?”, use a criteria-based formula such as SUMIFS. Combining criteria with visibility can require a more specialized formula, so avoid replacing SUBTOTAL with SUMIFS on the assumption that it will respond to a filter automatically.

Common problems and practical checks

The total does not change: Check that you used SUBTOTAL rather than SUM, that the filtered values fall inside the referenced range, and that the filter is applied to the rows you expect.
The total is too high: Confirm that the formula range does not include unrelated numbers or a separate total. SUBTOTAL ignores nested SUBTOTAL results, but a SUM elsewhere in the range can still be counted as an ordinary value.
The total is too low: Check for manually hidden rows. Code 109 excludes them; code 9 includes them. Also confirm that the values are numeric rather than text that only looks like numbers.
New records are missing: A fixed range such as B2:B100 will not include a record added in row 101. Extend the range or use a table’s structured reference so the formula can cover the growing data.
The formula shows an error: Check that the function number and range are separated by a comma, and that the range reference is valid. In some regional settings Excel displays a different list separator, even though comma syntax is used in the examples here.

For a quick check, clear all filters and compare the result with the known full-list total. Then apply a filter whose visible records are easy to add by hand. If those totals agree, the formula is probably referencing the intended values. Also check whether the workbook is set to manual calculation if a result appears stale; in Microsoft 365 on Windows, use Formulas > Calculation Options and select Automatic when appropriate. Mac ribbon placement may differ, but the calculation setting is available from Excel’s formulas controls.

Frequently Asked Questions

What formula sums only visible cells in Excel?
With the values in B2:B100, use =SUBTOTAL(109,B2:B100). It sums visible values and excludes both filtered-out rows and manually hidden rows.

Does SUBTOTAL update when I filter by a slicer?
Yes, when the slicer filters the table or PivotTable data represented by the referenced range, SUBTOTAL responds to the rows made visible. Make sure the formula refers to the data affected by that slicer.

What is the difference between SUBTOTAL 9 and 109?
Both perform SUM and ignore rows excluded by a filter. Code 9 includes manually hidden rows; code 109 excludes them. Use 109 when every hidden row should be left out.

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

Why does SUM still include filtered-out rows?
SUM totals its referenced values without considering whether their rows are visible. Replace it with SUBTOTAL when the result needs to change as a filter hides or reveals records.

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.