Excel Sum Based on Filter: Formulas for Visible Rows

To sum only the rows currently visible after filtering in Excel, use SUBTOTAL. With values in B2:B100, enter =SUBTOTAL(109,B2:B100); the total updates when you change the filter and excludes rows hidden manually as well.

What SUBTOTAL Does With Filtered Rows

Excel’s AutoFilter hides rows that do not match the selected criteria; it does not remove or rearrange the underlying data. A regular SUM still includes those filtered-out values. SUBTOTAL is designed to work with lists and filtered ranges: it ignores rows excluded by a filter, so its result reflects the visible records.

The first argument tells SUBTOTAL which calculation to perform. The number 9 means SUM, while 109 also means SUM but additionally ignores rows hidden manually. For an AutoFilter, both versions ignore filtered-out rows. This distinction matters if someone has separately hidden rows using the row commands or by hiding a row in the worksheet.

SUBTOTAL also ignores other SUBTOTAL formulas in its reference. That helps prevent a grand total from counting intermediate subtotals a second time. It does not, however, make every kind of hidden data disappear from the calculation: the function responds to hidden rows, not to font color, cell formatting, or a custom “hide” appearance.

Set Up a Filter-Aware Total

For a standard worksheet range in Excel for Microsoft 365 on Windows, put the total outside the rows being filtered. Select a cell in the data, then choose Data > Filter. Use the dropdown in the relevant column header to select the records you want to see. If the filterable data has headers and forms a continuous block, Excel applies dropdowns to the column headings.

With amounts in B2:B100 and the total in B101 or another cell outside the filtered records, enter =SUBTOTAL(109,B2:B100). The displayed result will be the sum of the visible cells in that range. Change a filter selection and Excel recalculates the subtotal against the rows that remain visible.

Include only the data rows in the reference. If the header is in row 1, a typical range begins at row 2.
Keep the subtotal cell outside the filtered list so it does not become another record or get hidden by the filter.
Check that the amount cells contain numbers. Values stored as text are not added as numeric amounts.
When you add records below a plain range, confirm that the formula and filter include the new rows.

The Data tab and Filter command are also available in Excel for Mac and Excel for the web, though the surrounding interface can differ slightly by platform and release. The SUBTOTAL formula itself works the same way. If calculation mode has been set to Manual, Excel may not refresh formulas immediately; use Formulas > Calculate Now or switch calculation back to Automatic.

Worked Example: Total the Visible Sales

Suppose a list has regions in column A and sales in column B. Rows 2 through 7 contain North 120, South 80, North 150, East 90, South 200, and North 60. If you filter column A to show North, the visible sales are 120, 150, and 60.

With the sales values in B2:B7 and the total in a cell outside the list, enter =SUBTOTAL(109,B2:B7). The result is 330, because 120 + 150 + 60 = 330. If you clear the region filter, all six sales values are visible and the result becomes 700: 120 + 80 + 150 + 90 + 200 + 60.

This makes SUBTOTAL useful for reports where a person changes the filter interactively and expects the total to follow the visible records. It is not necessary to rewrite the formula each time the selection changes. The same approach can total other numeric measures, such as hours, quantities, or expenses, by pointing the formula at the relevant column.

Choose Between 9 and 109

Use 109 when the result should omit both filter-hidden rows and rows hidden manually. Use 9 when you want to omit rows hidden by the filter but still include rows hidden manually. For a list that is controlled only by AutoFilter, the two options usually return the same result.

The function-number pairs from 1 through 11 and 101 through 111 cover calculations such as AVERAGE, COUNT, MAX, MIN, and SUM. The second group excludes manually hidden rows; both groups exclude rows hidden by a filter. For instance, 1 and 101 mean AVERAGE, and 2 and 102 mean COUNT. These are SUBTOTAL options, not separate functions.

That difference can explain a total that seems unexpectedly small. If a user hid a row manually and the formula uses 109, its value is left out even if the row would otherwise meet the filter criteria. If manual hiding should not affect the total, change the first argument to 9. Conversely, choose 109 when hidden rows should never contribute to the displayed result.

Use a Table for Growing Data

For a list that gains records regularly, converting the range to an Excel Table can reduce maintenance. Select a cell in the list and choose Insert > Table, then confirm that the table has headers. Tables provide filter controls in their header row and expand when data is added directly below them.

Assuming the table is named Sales and its numeric column is named Amount, place the total outside the table and enter =SUBTOTAL(109,Sales[Amount]). This structured-reference formula totals visible values in the Amount column and can include new table rows as the table expands. Excel commonly names a new table Table1; you can see or change its name on the Table Design tab.

Tables also offer a Total Row option on the Table Design tab. Its dropdown lets you select a calculation such as Sum, and Excel uses a SUBTOTAL-based calculation so the result responds to filtering. A formula outside the table is often preferable when the total needs a fixed location in a report; a Total Row is convenient when the summary should travel with the list.

SUBTOTAL Versus SUMIFS

SUBTOTAL answers “What is the sum of the rows visible under the current filter?” SUMIFS answers a different question: “What is the sum of rows matching criteria I specify in the formula?” SUMIFS does not automatically restrict its result to rows currently visible. A filter may hide matching records, but SUMIFS still evaluates the full referenced ranges.

For example, with region names in A2:A7 and sales in B2:B7, use =SUMIFS(B2:B7,A2:A7,"North") to total every North sale, whether or not the worksheet is filtered. In the example above, the result is 330. If a filter hides one of the North rows, the formula still includes that row. Use SUMIFS when the criteria should be fixed in the formula, and SUBTOTAL when the visible filter selection should control the total.

The two functions are not interchangeable. A SUMIFS formula can calculate totals for several named categories in a dashboard without changing the worksheet filter. A SUBTOTAL formula is more responsive to a person exploring the list with dropdowns. If a report needs both kinds of answers, use clearly labeled cells for the filter-dependent visible total and the criteria-based total.

Common Problems and Checks

The total does not change: Check that the formula uses SUBTOTAL rather than SUM, that the filter actually hides rows within the referenced range, and that the formula range covers the relevant values. If calculation is manual, recalculate the workbook.
The total is too high or too low: Look for values outside the range, manually hidden rows when using 109, or numbers stored as text. Also check for extra rows included in the list and whether the filter is applied to the expected column.
New records are missing: A fixed reference such as B2:B100 will not automatically grow past row 100. Extend the formula and filter range, or use an Excel Table with a structured reference.
The result shows an error: A visible error value in the cells being summed can cause the subtotal to return an error. Find and correct the source error rather than assuming the formula has ignored it.
Rows are colored but still counted: Color alone does not hide a row. SUBTOTAL responds to actual filtered or hidden rows, not to formatting. Apply a filter that hides the unwanted records or use a criteria-based formula for a different calculation.

Be careful with copied formulas in multiple summary areas. SUBTOTAL ignores other SUBTOTAL formulas when they fall inside its reference, which is useful for avoiding double-counting. But a formula that references the wrong column or stops short of the last record can still produce a plausible-looking but incomplete answer. Inspect the range in the formula bar when the total does not match expectations.

When Another Approach Is More Suitable

If the requirement is a fixed condition—such as total sales for North, regardless of what a user filters—SUMIFS is generally the clearer choice. If the question is about visible records after applying AutoFilter, SUBTOTAL is the straightforward built-in option. For a more complex report that must combine current visibility with additional conditions, consider adding a helper column or using a PivotTable, and verify how its filters and totals are configured.

A PivotTable is especially useful when readers need to group and summarize many fields, rather than simply total one filtered column. It can show subtotals by region, product, or date and lets a reader filter the summarized result. However, a PivotTable is a separate summary view; it is not the same as a SUBTOTAL formula next to a filtered source list. Choose based on whether the reader needs a live total of visible source rows or a grouped analysis.

Frequently Asked Questions

Does SUM change when I filter rows in Excel?
No. SUM normally adds all numeric cells in its reference, including rows hidden by an AutoFilter. Use SUBTOTAL with function number 9 or 109 when the total should reflect filtered visibility.

What formula sums only visible cells in Excel?
Use SUBTOTAL with 109 for a sum that excludes filtered rows and manually hidden rows. Use 9 if manually hidden rows should still count. Both options ignore rows hidden by an AutoFilter.

Will SUBTOTAL update when I change a filter?
Yes. When an AutoFilter changes which rows are visible, a SUBTOTAL formula over the relevant range recalculates to reflect the visible rows, provided Excel is calculating automatically or the workbook is recalculated.

Does SUMIFS ignore filtered-out rows?
No. SUMIFS checks its criteria against the referenced cells whether or not their rows are visible. Use it for formula-defined conditions; use SUBTOTAL when the worksheet’s current filter should determine which rows contribute.

Can I use SUBTOTAL with an Excel Table?
Yes. A SUBTOTAL formula can reference a table column, such as Sales[Amount], and respond to that table’s filters. A Table Total Row is another convenient option for a filter-aware summary.

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.