Excel Sum Filtered List: SUBTOTAL and SUM

To sum only the rows currently visible after filtering in Excel, use =SUBTOTAL(109,B2:B6), assuming the values are in B2:B6. SUBTOTAL recalculates as the filter changes; the 109 option also excludes rows you hide manually.

The quick answer: use SUBTOTAL

For a normal worksheet range, SUBTOTAL is the straightforward way to total a filtered list. Apply a filter to the data, then put the formula in a cell outside the rows being filtered. Excel leaves filtered-out records out of the result and updates the total when you change the filter.

For example, assume categories are in A2:A6 and amounts are in B2:B6. The five amounts are 120, 80, 150, 70, and 200. If you filter column A to show only North and those records have amounts 120, 150, and 200, the formula =SUBTOTAL(109,B2:B6) returns 470. If you clear the filter, the result becomes 620, the sum of all five amounts.

Keep the total outside the filtered records, such as above the list or in a separate summary area. A formula placed in a row that is itself filtered away may also disappear from view, which can make it seem as if the calculation stopped working.

How to filter the list and add the total

Make sure the data has a single header row and no completely blank rows or columns splitting the list.
Select a cell in the list, then choose Data > Filter. Excel adds a dropdown to each header.
Open the dropdown for the column you want to filter, select the values to keep, and confirm the selection.
Choose a cell outside the data and enter the formula. With amounts in B2:B6, use =SUBTOTAL(109,B2:B6).
Change or clear the filter and check that the displayed total changes with the visible records.

On Windows, Ctrl+Shift+L toggles AutoFilter for a selected range or table. On Mac, use Data > Filter if your keyboard shortcut differs by Excel version or keyboard settings. The ribbon command is also useful when you are unsure whether the current selection includes the intended header row. In Excel for the web, the Data tab provides filtering controls, though the exact ribbon layout can differ slightly.

Why SUBTOTAL responds to filters

SUBTOTAL is designed for calculations on lists and grouped data. Its first argument is a function number that selects the calculation, and the remaining argument is the range or ranges to calculate. Function number 9 means SUM. The corresponding number 109 also means SUM, but adds an important rule: it ignores rows hidden manually as well as rows hidden by a filter.

Both 9 and 109 ignore rows that an AutoFilter removes from view. That is why either can produce the same answer for a filtered list. Choose 109 when you want the total to represent only rows that are actually visible, including when someone has also hidden individual rows using the row-header context menu. Choose 9 when manually hidden rows should still count, while filtered-out rows should not.

SUBTOTAL also avoids counting another SUBTOTAL result inside its reference range. This can help prevent double-counting when a list contains intermediate totals. It does not, however, identify a business category or apply a separate condition by itself: the worksheet filter determines which records are visible.

Choosing between 9 and 109

The difference matters because filtering and manually hiding rows are separate actions. A filter hides records that do not meet the selected criteria. Manually hiding a row changes its visibility without changing the filter criteria. The function number determines whether the latter rows contribute to the sum.

Use 109 when the requested answer means “sum every row currently visible.” This is usually the least surprising choice for a report users may filter and hide.
Use 9 when the report should exclude filter results but still include rows that someone manually hid.
Use SUM when hidden and filtered rows should all remain part of the total. SUM does not adjust its result merely because rows are hidden.

For instance, with amounts in B2:B6, if a filter leaves rows worth 120, 150, and 200 visible, both SUBTOTAL options return 470. If someone then manually hides the row containing 150, 109 returns 320, while 9 still returns 470. This distinction is a common source of totals that appear inconsistent between two similar reports.

Using SUBTOTAL with an Excel table

Excel tables make filtered lists easier to manage because filters are included in the table headers and typically extend automatically when new rows are added. To create one, select the data and choose Insert > Table, then confirm that the table has headers if it does. You can also enable a table’s Total Row from the Table Design tab.

Assume a table named Sales has a column named Amount. In a cell outside that table, the formula =SUBTOTAL(109,Sales[Amount]) sums visible amounts in the table and follows its filters. Excel table references are easier to read than fixed cell addresses, and they usually expand as records are added to the table. If your table or column has a different name, use that name in the reference.

A table Total Row is another convenient option. When you turn it on, choose the Amount column’s total cell and select Sum from its dropdown. Excel uses a subtotal-style calculation so the total can respond to table filtering. This is usually easier for a compact report; a separate SUBTOTAL cell gives you more control over where the result appears and how it is labeled.

Filtered rows versus a separate criteria-based sum

A filtered list and a criteria-based calculation are related but not identical. SUBTOTAL answers, “What is the total of the rows visible right now?” A conditional formula answers, “What is the total of rows that meet this condition?” The latter can be useful when you want a result that does not depend on which dropdown selections a reader has made.

Assume categories are in A2:A6, amounts are in B2:B6, and cell C1 contains the word North. In Microsoft 365 or Excel 2021 and later, =SUM(FILTER(B2:B6,A2:A6=C1)) returns 470 for the North records in this example. FILTER creates a result based on the category condition; it does not mean “use only the rows currently visible after AutoFilter.” If the worksheet filter hides a North row, this calculation can still include it because the formula evaluates the source data against C1.

That difference helps prevent a common mistake: using a condition-based sum when the question is about the current visible selection. Use SUBTOTAL for the interactive filter state. Use a criteria-based approach when the condition itself should be specified in a cell or formula and should remain independent of the user’s filter choices.

Common problems and how to fix them

The total includes every record. Check that you used SUBTOTAL rather than SUM and that the filter actually hid rows. If the rows are only formatted or marked, but not filtered out or hidden, SUBTOTAL will include them.
The total changes when you hide a row. That is expected with function number 109. Use 9 if manually hidden rows should count; both options still exclude filtered-out records.
The total is zero or too small. Check that the referenced cells contain numeric values rather than numbers stored as text, and confirm the range covers the whole amount column. Text entries are not added as numbers.
The result does not update as expected. Confirm the filter is applied to the same list as the referenced range. Also check Excel’s calculation setting: with automatic calculation turned off, formulas may not recalculate until you request a calculation or restore automatic calculation under Formulas > Calculation Options.
The total row gets filtered out. Move the formula outside the filtered data range, or use a table Total Row. A total placed among ordinary records is subject to the same filter as those records.

If a list has blank rows, merged header cells, or multiple rows of headings, Excel may not identify the intended range cleanly when you turn on filtering. Convert a contiguous, well-labeled range to a table when practical. That gives the data a clear boundary and makes it less likely that a new record will be left outside the filter or total range.

Other ways to total visible data

For a straightforward filtered list, SUBTOTAL is usually simpler and clearer than building a helper column. A helper column can still be useful when you need to inspect or reuse a row-by-row condition, but it requires extra setup and can be easier to misconfigure. PivotTables are another option when the goal is to summarize amounts by categories, compare groups, or rearrange a report; they are more powerful than a single filtered total but require a different reporting workflow.

SUM is appropriate when visibility should not affect the answer, while a conditional sum is appropriate when a defined criterion should control the answer. These methods are not interchangeable. Before choosing one, decide whether the intended total follows the current filter, a fixed condition, or every record in the source.

Frequently Asked Questions

Does SUM ignore filtered-out rows?
No. SUM normally includes values in hidden or filtered-out rows if they are in its range. Use SUBTOTAL when the result should change with an AutoFilter.

Should I use SUBTOTAL 9 or 109?
Both ignore rows hidden by a filter. Use 109 if manually hidden rows should also be excluded; use 9 if manually hidden rows should remain in the total.

Can SUBTOTAL sum only the rows visible after filtering?
Yes. Use its SUM function number, 9 or 109, and reference the amount range. The calculation updates when the filter changes.

Does SUBTOTAL work with Excel tables?
Yes. You can reference a table column by name, or enable the table’s Total Row and select Sum for the relevant column.

Will a criteria-based FILTER formula follow AutoFilter?
Not automatically. FILTER selects records using the condition in its formula. To total only the rows currently visible because of a worksheet filter, use SUBTOTAL instead.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.