Excel SUM Function: Does It Count Filtered Values?

Formulas & functions · Updated November 18, 2026

Yes. In Excel for Microsoft 365, SUM adds every numeric value in its referenced range, including values in rows hidden by a filter. To total only the visible rows, use SUBTOTAL instead; the choice between its function numbers 9 and 109 matters if you also hide rows manually.

What SUM does when you filter a range

An Excel filter hides rows that do not match the selected criteria; it does not remove those rows from the worksheet or from a range reference. The SUM function does not check whether a row is visible. It adds the numeric values in the referenced cells whether their rows are displayed, filtered out, or manually hidden.

For example, imagine a sales list with amounts of 10, 20, 30, 40, and 50 in cells A2:A6. If a filter leaves only the rows with 40 and 50 visible, a sum of the entire range is still 150. The visible amounts total 90, but filtering alone does not change what SUM includes.

Strictly speaking, SUM does not count entries; it adds numbers. People often say it counts filtered values to ask whether the hidden rows are included in the total. The answer is yes: filtering changes what you see, not the values included by an ordinary SUM range.

Use SUBTOTAL for a visible-row total

SUBTOTAL is designed for calculations over lists where rows may be filtered. It excludes rows removed from view by an AutoFilter, so its displayed result changes as you change the filter. For a sum, use function number 9 or 109.

With the example amounts in A2:A6 and only 40 and 50 visible after filtering, enter =SUBTOTAL(9,A2:A6). The result is 90 because SUBTOTAL ignores the filtered-out rows.

The function number controls whether manually hidden rows are included. SUBTOTAL(9,...) includes manually hidden rows, while still excluding rows filtered out by AutoFilter. SUBTOTAL(109,...) excludes both filtered-out rows and rows you hide manually.

With the same values in A2:A6, and with the row containing 40 manually hidden while the rows for 40 and 50 otherwise meet the filter, =SUBTOTAL(109,A2:A6) returns 50. The version using 9 returns 90 because it includes the manually hidden 40.

Choose based on what visible means for your report:

  • Use 9 when the total should respond to filters but include rows hidden manually.
  • Use 109 when the total should include only rows currently displayed, excluding manually hidden rows too.

How to add a filter and check the result

In Excel for Microsoft 365 on Windows, select a cell in the data list and choose Data > Filter. Use the arrow in the relevant column heading to select the criteria. The same calculation behavior applies in Excel for Mac and Excel for the web, although ribbon layout and available window space can differ.

To compare the full-range total with the visible-only total, first identify the amount column and its data rows. If the amounts occupy B2:B100, put an ordinary total and a visible-only total in separate cells. With the amounts in B2:B100, =SUM(B2:B100) returns the total for all rows, regardless of the filter. With those same amounts, =SUBTOTAL(109,B2:B100) returns the total for visible rows, excluding manually hidden rows as well.

Keep the summary formulas outside the list being filtered when practical. If you place a total row within the filtered range, it may itself be hidden by the criteria. Also avoid selecting only the currently visible cells when entering the formula: SUBTOTAL is what makes the result respond automatically as the filter changes.

What happens with Excel tables

Excel tables add filter buttons to their header row and expand as data is added. Their filtering does not change SUM’s rule: a formula that references the whole amount column still includes filtered-out records.

For a table named SalesData with a column named Amount, a visible-only total can use the structured reference =SUBTOTAL(109,SalesData[Amount]). It updates as table rows are filtered, and the structured reference includes new table records as the table grows. Replace SalesData and Amount with the actual table and column names in your workbook.

A table’s Total Row is another convenient option. Select a cell in the table, open the Table Design tab, and enable Total Row. In the total cell under the numeric column, choose the calculation from its dropdown; for a filtered-aware sum, select Sum, which uses a subtotal calculation. Check the formula bar if you need to confirm the exact formula Excel inserted.

SUM, SUBTOTAL, and related formulas

Use SUM when you want all records included irrespective of what a filter displays—for example, a fixed grand total for a complete dataset. Use SUBTOTAL when the answer should describe the current filtered view. This distinction is especially important in reports: a label such as Visible sales should not sit beside a SUM that still includes excluded records.

Criteria functions such as SUMIF and SUMIFS also do not automatically become visible-row totals just because a filter is applied. They evaluate their stated criteria across the referenced data. If you need both criteria-based selection and visibility-aware results, the right approach depends on the workbook layout and criteria; do not assume that adding a filter makes a SUMIF result filter-sensitive.

SUBTOTAL also supports functions besides sum, including average and count. Its function number determines the operation. A further useful behavior is that SUBTOTAL ignores other SUBTOTAL formulas within its reference range, which helps prevent double-counting in lists with intermediate subtotal rows. It does not ignore ordinary SUM formulas nested in the range in the same way.

Common problems and checks

If the visible-only total seems wrong, check the formula and the rows before changing the filter. A few common causes are:

  • The formula is SUM: it includes filtered-out rows. Replace it with SUBTOTAL if the total should follow the current view.
  • You chose 9 but hid rows manually: function number 9 includes manually hidden rows. Use 109 to exclude them.
  • The range misses records: confirm the first and last data rows are included. A table reference can reduce the chance of leaving out newly added records.
  • Some amounts are stored as text: SUM adds numeric values, not numbers stored as text in a way Excel does not recognize. Check the cell format and the underlying values if expected amounts are absent.
  • An error appears: look for error values in the referenced data or an incorrectly written range. Filtering does not make an ordinary SUM ignore errors in its range.

Also confirm that the filter is actually applied to the rows you intend. A visible subtotal may be correct for one filtered column while a reader assumes the data is filtered on another. Clear the filter using the column dropdown if you need to restore the full view; the SUBTOTAL result will then reflect all rows that are not manually hidden when you use 109.

Frequently Asked Questions

Does SUM ignore rows hidden by a filter?
No. SUM includes numeric values in filtered-out rows if those rows are part of its referenced range. Use SUBTOTAL when the total should change with the filter.

Which formula sums only visible cells in Excel?
Use SUBTOTAL with function number 109 to sum visible rows while excluding rows hidden by filters and rows hidden manually. Function number 9 excludes filtered-out rows but includes manually hidden rows.

Does SUBTOTAL update when I change a filter?
Yes. For a range containing a filtered list, SUBTOTAL recalculates its result based on the rows currently shown. The total can change when you select different filter criteria.

Does SUMIF respect filtered rows?
No. SUMIF applies its own condition to its referenced data; an AutoFilter does not make it ignore hidden records. Use a visibility-aware method if the result must reflect the displayed rows.

Why does my total change when I hide a row manually?
A SUBTOTAL using 109 excludes manually hidden rows, so hiding one can reduce the result. A SUBTOTAL using 9 includes manually hidden rows. An ordinary SUM includes them too.

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.