To sum only the rows currently visible after filtering in Excel, put =SUBTOTAL(9,C2:C7) in a cell outside the filtered range, replacing C2:C7 with your numbers. For a sum that also excludes rows hidden manually, use =SUBTOTAL(109,C2:C7).
What the filtered-data total means
An Excel filter hides rows that do not meet the selected criteria; it does not delete them. A normal sum, such as =SUM(C2:C7), includes numbers in both visible and filtered-out rows. That can make a displayed total look inconsistent with the records on screen.
For a concrete example, imagine a list with headers Item, Region, and Sales in A1:C7. The six sales values in C2:C7 are 120, 80, 90, 50, 75, and 100; their regions in B2:B7 are North, South, North, West, North, and South. If you filter Region to show only North, the visible sales are 120, 90, and 75, which add up to 285. A filtered-data total should show 285, not the all-row total of 515.
This distinction matters for reports, inventory lists, expense records, and any worksheet where people change filters to inspect a subset. The key question is whether the result should follow the visible rows or calculate from a condition regardless of what is currently displayed.
How SUBTOTAL responds to filters
SUBTOTAL is designed for totals and other calculations on lists. Its first argument selects the operation: 9 means SUM. When Excel filters a range, SUBTOTAL excludes rows removed by the filter automatically. With sales in C2:C7 and the North filter applied, =SUBTOTAL(9,C2:C7) returns 285. Clear the filter and it returns 515.
SUBTOTAL also has a useful behavior when the referenced range contains other SUBTOTAL formulas: it ignores those nested subtotals, helping prevent a report from counting intermediate totals again. Put the formula in a cell outside the rows it totals, such as below the data, so it does not include its own result.
A filter can be applied to a regular range or an Excel Table. Select a cell in the list and choose Data > Filter to add filter controls to the headers. Then use a header’s drop-down to select a value or apply a condition. The formula recalculates as the visible rows change; there is no need to edit it for each filter selection.
Choose 9 or 109
The difference between 9 and 109 is how SUBTOTAL treats rows hidden manually, rather than hidden by a filter. Both versions exclude rows removed by a filter. The 9 version includes manually hidden rows; 109 excludes them as well.
- Use
=SUBTOTAL(9,C2:C7)when the total should include rows hidden with Excel’s Hide command but exclude rows filtered out. - Use
=SUBTOTAL(109,C2:C7)when the total should include only rows that are visible, excluding both filtered-out rows and manually hidden rows.
For example, if North is filtered and the row containing the 90 sale is then manually hidden, SUBTOTAL with 9 still returns 285. With 109, it returns 195, because only 120 and 75 remain visible. If no rows have been hidden manually, the two formulas return the same result.
This distinction is easy to miss when a worksheet uses grouped or manually hidden rows. Decide what “visible” means for your report before choosing the function number. If users only change filter drop-downs, 9 is often sufficient; if hiding rows should also remove them from the total, choose 109.
Apply a filter and check the result
For the example list, keep the headers in row 1 and the records in rows 2 through 7. Put the total in C8 or another cell outside the filtered records. With sales in C2:C7, enter =SUBTOTAL(9,C2:C7). After enabling filters with Data > Filter, choose North in the Region header. The visible records have sales of 120, 90, and 75, so the total should be 285.
- Check that the filter covers the full list, including the Sales column. If the filter was created for only part of a list, some records may not respond as expected.
- Enter the SUBTOTAL formula outside the data rows. This keeps the total from being treated as another record and avoids accidental self-reference.
- Change the filter and compare the total with the visible numbers. Clear the filter to confirm that the example returns 515.
In Microsoft 365 for Windows, the ribbon path is Data > Filter. Excel for Mac also provides the Filter command on the Data tab; labels and layout can vary slightly by release. The SUBTOTAL formula works across these desktop versions. In Excel for the web, use the Data tab’s filter controls when available; the formula still recalculates from the filtered list.
Use FILTER when the criteria belong in a formula
SUBTOTAL follows the worksheet’s current filter state. A different approach is to calculate a sum for a stated condition directly in a formula. The FILTER function is useful when you want a result for a particular category even if the user has not applied that category as a sheet filter. FILTER is available in Microsoft 365 and Excel 2021 or later.
With regions in B2:B7 and sales in C2:C7, =SUM(FILTER(C2:C7,B2:B7="North",0)) returns 285. The condition selects the sales corresponding to North, and SUM adds those results. The final argument, 0, supplies a value if there are no matching rows, so the formula returns zero instead of a no-match error.
FILTER is not a replacement for a formula that must respond to the visible rows. It evaluates its stated condition against the source data; changing a header filter does not turn that condition into “whatever is currently visible.” Use SUBTOTAL for a total tied to current visibility, and FILTER when the formula itself should define the records to include. This difference is useful when building dashboards with a chosen category or criteria stored in a cell.
How SUMIF and SUMIFS compare
SUMIF and SUMIFS also calculate by criteria, not by visibility. They are often a simpler option than FILTER when the goal is to total one or more categories across the whole data range. They do not automatically ignore rows hidden by AutoFilter.
With regions in B2:B7 and sales in C2:C7, =SUMIF(B2:B7,"North",C2:C7) returns 285, whether or not North is currently selected in the worksheet filter. If the sheet is filtered to South, this formula still calculates the total for North from all records. For multiple criteria, use SUMIFS; for example, it can total sales for a region and an item category at the same time. The ranges supplied to SUMIFS must be compatible in size.
A useful rule is to choose the function based on what should control inclusion. Use SUBTOTAL when the visible rows control the answer, SUMIF or SUMIFS when criteria control it, and SUM(FILTER(…)) when you want a formula-driven filtered array that can be combined with other calculations.
Using an Excel Table
Excel Tables make filtered lists easier to maintain because their header filters are built in and their ranges expand as records are added. To create one, select a cell in the data and choose Insert > Table, then confirm that the table has headers. You can also use the Table Design tab to give it a meaningful name, such as SalesTable.
With an Excel Table named SalesTable and a numeric column named Sales, use =SUBTOTAL(109,SalesTable[Sales]) in a cell outside the table. It sums visible sales and excludes manually hidden rows. The structured reference refers to the Sales column rather than a fixed address, so added table records are included automatically. Check the table’s actual name and column heading if your formula shows an error; structured references must match them.
A Table does not change the basic behavior of SUBTOTAL. The advantage is that the formula is less likely to miss new records when the list grows. A regular range is fine for a stable list, but a Table can be more convenient for recurring reports and data that is updated regularly.
Common problems and checks
- The total includes filtered-out records: Check whether the formula uses SUM instead of SUBTOTAL. SUM does not adjust its total to AutoFilter visibility.
- The total changes unexpectedly after hiding a row: A function number of 9 includes manually hidden rows, while 109 excludes them. Confirm which behavior the report needs.
- The total is zero or too small: Check that the range covers the intended numeric cells and that the values are numbers rather than text. Values stored as text may not be added as expected by a sum.
- The total does not change when filtering: Confirm that the filter applies to the records included in the formula, and that the total cell is outside the filtered data rows.
- The total is higher than the visible values: Look for manually hidden rows if using 9, or check whether the formula includes extra cells outside the list.
Also remember that SUBTOTAL is not a general error-cleanup function. If a visible cell in the sum range contains an error, the result can show that error. Find and correct the source value, or handle errors deliberately in a separate formula, rather than assuming the filter-aware total will ignore them.
Frequently Asked Questions
Does SUM ignore rows hidden by an Excel filter?
No. SUM includes numbers in filtered-out rows within its range. Use SUBTOTAL with function number 9 or 109 when the total must respond to filtered visibility.
What formula sums only visible cells in Excel?
Use =SUBTOTAL(109,C2:C7) with the relevant values in C2:C7. It ignores rows excluded by a filter and rows hidden manually. Use 9 instead if manually hidden rows should still count.
Does SUBTOTAL update when I change a filter?
Yes. SUBTOTAL recalculates to reflect rows hidden or revealed by the filter. In the example above, selecting North returns 285 and clearing the filter returns 515.
Can I use SUBTOTAL with an Excel Table?
Yes. A structured reference such as =SUBTOTAL(109,SalesTable[Sales]) sums the visible values in the Sales column of a table named SalesTable. Replace both names with the names used in your workbook.
Why does SUMIF give a different result from SUBTOTAL?
SUMIF applies its stated condition to the data, including rows hidden by a filter. SUBTOTAL follows filtered visibility. The results differ when the condition’s matching rows are not the same as the rows currently shown.
Related
- Small Excel Table: Create, Format, and Use One
- Small Excel Sample Data: Examples to Copy and Use Now
- Large Excel Data: Limits, Tools, and Best Practices
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Sort Excel by Number: Steps, Options, and Common FixesTo sort numbers in Excel, select a cell in the number column, then choose Data > Sort Smallest…
- Excel Attendance List: Setup, Formulas, and Practical TipsAn Excel attendance list is a worksheet that records who attended each class, meeting, or event and makes…
- IFERROR with VLOOKUP in Excel: Hide Lookup ErrorsYes. With lookup IDs in A2:A100, return values in C2:C100, and the value to search for in E2,…
- Excel LEN Function Examples for Text, Spaces, and DataExcel’s LEN function returns the number of characters in a value, including spaces. For example, if A2 contains…
- How to Calculate a Unique Count in Excel with FormulasTo count distinct, nonblank values in Excel for Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0), replacing A2:A100 with your data range.…
- Excel SEQUENCE Shortcut: Fast Ways to Create a SeriesExcel has no dedicated keyboard shortcut for the SEQUENCE function. In Microsoft 365 or Excel 2021 and later,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.