To sum only the rows currently visible after filtering in Excel, use SUBTOTAL with function number 109: =SUBTOTAL(109,B2:B100). Replace the range with the cells containing your numbers; Excel will recalculate the total as you change or clear the filter.
Why SUM does not give a filtered total
The ordinary SUM function adds every numeric cell in its range, including rows hidden by an AutoFilter. Filtering changes which records are displayed, not which values exist in the worksheet, so a regular sum can continue to include records you cannot currently see.
SUBTOTAL is designed for this situation. It calculates a chosen operation over a range while excluding rows removed by a filter. When you filter a list by a category, date, salesperson, or other field, the subtotal adjusts to include only the remaining visible records. Clear the filter and the formula returns to the total for all rows.
The function number 109 means “sum while ignoring manually hidden rows as well as filtered-out rows.” If you only need to ignore filters and want manually hidden rows included, use 9 instead. That difference matters when someone hides rows using the row-header menu, rather than filtering the list.
How SUBTOTAL works
The syntax places a function number first and one or more cell references after it. For a filtered sum, the relevant choices are:
9performs a sum, excludes rows hidden by a filter, and includes rows hidden manually.109performs a sum, excludes rows hidden by a filter, and also excludes rows hidden manually.
In both cases, filtered-out rows are omitted automatically. The 100-series number changes how manually hidden rows are treated; it is not a stronger filter setting. The formula does not need to know which field you filtered or what criteria you selected. It responds to which rows are visible.
SUBTOTAL also avoids including another SUBTOTAL result within its reference, which helps prevent accidental double-counting when lists contain subtotal calculations. It is not a general replacement for every summary formula, however: it calculates one specified operation over a range and does not apply a separate condition such as “sum only orders from the West region” unless that condition is applied as a filter.
Worked example with a filtered list
Suppose a worksheet has Category in A1 and Amount in B1, with these records in rows 2 through 7: North, 120; South, 80; North, 150; East, 90; South, 110; and North, 60. If the list is filtered to show North, the visible amounts are 120, 150, and 60, which add up to 330.
With those headers in A1:B1 and amounts in B2:B7, put the total in B8, outside the rows being filtered, and enter =SUBTOTAL(109,B2:B7). The displayed result is 330 when North is selected. If you change the filter to South, the visible values are 80 and 110, so the result becomes 190. Clear the filter and all six amounts are visible; the total becomes 610.
The arithmetic is 120 + 80 + 150 + 90 + 110 + 60 = 610. This is a useful check when building a report: compare the filtered result with the visible values, then clear the filter and confirm the overall total.
Set up the filter and total
- Make sure the data has a header row and each column contains one kind of information, such as category, date, or amount.
- Click a cell in the list. On Windows, choose Data > Filter, or use Ctrl+Shift+L. In Excel for Mac, use Data > Filter; the keyboard shortcut is Command+Shift+F.
- Open the drop-down arrow in the column you want to filter, select or enter the criteria, and apply the filter.
- Click a cell outside the filtered records, such as a total row below the list but outside the filter range.
- Enter a SUBTOTAL formula referencing the numeric column. If the amounts are in B2:B100, use
=SUBTOTAL(109,B2:B100).
Keep the formula outside the rows that the filter can hide, or use an Excel table’s Total Row. If the formula is part of the filtered records, it may disappear when a filter hides that row, making the result hard to find. Also check that the selected range includes every data row but not unrelated amounts elsewhere on the sheet.
Choosing between 9 and 109
For most filtered reports, 109 is a sensible choice because it excludes both filtered-out and manually hidden records. This makes the displayed result match the rows currently visible, including when a coworker has hidden a row separately. If your intention is specifically to ignore filter selections but include rows hidden by a user, choose 9 instead.
For example, with amounts in B2:B20, =SUBTOTAL(9,B2:B20) includes a manually hidden row in its total, while =SUBTOTAL(109,B2:B20) leaves that row out. Either formula still excludes rows removed by the active filter. If a total seems unexpectedly low, check whether any rows were manually hidden and whether the formula uses 109.
Using SUBTOTAL in an Excel table
Converting a range to an Excel table can make filtered lists easier to manage. Select a cell in the data and choose Insert > Table, then confirm that the table has headers. Excel tables provide filter buttons in their header row and expand when new records are added.
To add a total row, click in the table and use the Table Design tab’s Total Row option. In the total row, open the cell’s drop-down and choose Sum for the numeric column. Excel uses a SUBTOTAL calculation for table totals so the result responds to filtering. This is convenient when records are added over time because the table and its total can include new rows without manually extending a fixed range.
When entering your own formula with a table, use a structured reference that identifies the table column. With a table named Sales and a numeric column named Amount, enter =SUBTOTAL(109,Sales[Amount]) in a cell outside the table. The formula refers to the table’s Amount column and adjusts as the table grows. If your actual table or column has a different name, use that name instead.
SUBTOTAL compared with SUMIF and SUMIFS
SUMIF and SUMIFS add values that meet stated conditions, such as a particular department or date range. They do not ordinarily stop counting a matching row just because an AutoFilter hides it. That is different from SUBTOTAL, whose key purpose here is to total visible rows.
Use SUBTOTAL when the user changes the worksheet’s filter and the desired result is “sum what I can currently see.” Use SUMIF or SUMIFS when the result should follow a rule written into the formula, regardless of which rows happen to be displayed. If a task requires both fixed conditions and visibility-aware totals, plan the calculation carefully; a plain SUMIFS is not a substitute for a filtered subtotal.
Another common misconception is that selecting visible cells and reading Excel’s status bar creates a reusable worksheet result. The status bar can show a quick selection summary, but it is not a cell formula for a report. SUBTOTAL gives you a result in a cell that can be formatted, referenced, and included in a dashboard.
Common problems and checks
- The total does not change: Confirm that the list is actually filtered and that the formula references the rows being filtered. A normal SUM will not react to the filter.
- The result is zero or too small: Check whether the amount cells contain numbers or numeric-looking text. Text values are not added as numbers. Also check for manually hidden rows when using 109.
- New records are missing: A fixed reference such as B2:B100 will not cover records below row 100. Extend the reference or convert the data to a table.
- The total vanishes: The formula may be inside the filtered range and its row may have been hidden. Put it outside the range or use the table Total Row.
- The total counts the wrong records: Check the filter criteria and confirm that the formula’s reference is the amount column, not a neighboring field.
When diagnosing a discrepancy, first clear all filters and compare the subtotal with the expected full-list total. Then apply one filter at a time and check the visible records. This separates a formula-range problem from a filter selection or data-quality problem.
Frequently Asked Questions
Does SUBTOTAL ignore rows hidden by a filter?
Yes. Both SUBTOTAL(9,...) and SUBTOTAL(109,...) exclude rows hidden by an AutoFilter. The difference is how they treat rows hidden manually: 9 includes them, while 109 excludes them.
Can I use SUBTOTAL for a filtered column in Excel 365?
Yes. SUBTOTAL is available in Excel 365, and the same basic formula works in current desktop Excel for Windows and Mac, as well as Excel for the web. Use a reference covering the values you want to total.
Will SUBTOTAL work with multiple filters?
Yes. If you filter more than one column, the formula sums only records that remain visible after all active filters are applied. It does not require a separate formula for each filter.
Why does SUMIFS include filtered-out rows?
SUMIFS checks its specified criteria, not whether a row is currently visible. A matching row can still be included even when an AutoFilter hides it. Use SUBTOTAL when visibility itself should control the sum.
How do I total visible rows without filtering?
Use the 109 version if you want SUBTOTAL to omit rows manually hidden with Excel’s Hide command as well. For instance, with amounts in B2:B20, =SUBTOTAL(109,B2:B20) sums the rows that are not hidden. For a filter-driven report, the same formula also excludes rows filtered out of view.
Related
- Monthly Excel Budget Format: Build a Simple Template
- Year Calendar in Excel: Create a 12-Month View
- When Was Excel Created? The 1985 Launch Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Trees Full of Money Debt Snowball Spreadsheet GuideA debt snowball spreadsheet tracks balances, minimum payments, interest, and extra payments so you can direct available money…
- How to Make Columns in Word After a Title or HeadingTo put text in columns below a full-width title in Microsoft Word, select the body text and choose…
- How to Automatically Add Page Numbers in Microsoft WordTo automatically add page numbers in Word, open the document and choose Insert > Page Number, then select…
- Schedule an Email in Outlook Desktop: Step-by-StepTo schedule an email in Outlook desktop, compose your message and choose a future send time: in new…
- Excel Knowledge for Data Analysis: Skills That MatterExcel knowledge for data analysis means being able to organize data, clean it, calculate useful measures, summarize patterns,…
- Add a Dropdown List in Excel: Steps and TipsTo add a dropdown list in Excel for Microsoft 365 on Windows, select the cell or cells, go…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.