To sum only the visible values in a filtered Excel column, use =SUBTOTAL(9,B2:B7), replacing B2:B7 with your data range. SUBTOTAL recalculates as you change a filter; use function number 109 instead of 9 if you also want to exclude rows you hide manually.
Why SUM does not follow a filter
Excel’s SUM function adds every number in its referenced range, including values in rows that an AutoFilter temporarily hides. Filtering changes which rows are displayed, not the underlying data or the range itself. That is why a normal total can appear unchanged even after you filter a list down to a few records.
For example, suppose column A contains regions and column B contains sales, with North and South records mixed together. Filtering column A to show North hides the South rows, but SUM still adds every number in column B. SUBTOTAL is designed for lists and ranges where rows may be filtered: it can calculate using the rows currently visible. This is useful for sales reports, inventory lists, survey results, and other data you inspect by applying different filters.
The basic SUBTOTAL formula
With sales values in B2:B7, where the North records are 120, 150, and 200 and the South records are 80, 50, and 100, filter the Region column to North. Enter this formula in a cell outside the filtered list:
With the amounts in B2:B7, the formula is =SUBTOTAL(9,B2:B7). It returns 470, the sum of the three visible North values: 120 + 150 + 200. If you clear the filter, it returns 700, the total of all six values.
The first argument, 9, tells SUBTOTAL to perform a SUM calculation. The second argument is the range to calculate. Place the result outside the rows being filtered—for example, above the list or in a separate summary area—so it remains visible and does not become part of the data range by accident.
SUBTOTAL can also accept more than one reference, but a single continuous range is usually easiest to maintain. If the list grows, consider converting it to an Excel table so the data range and its total can expand together.
Choose between 9 and 109
Both function numbers calculate a sum and both exclude rows removed by an AutoFilter. Their difference is how they treat rows hidden manually using Excel’s Hide command or row-height changes:
- 9 includes manually hidden rows in the calculation, but excludes rows hidden by a filter.
- 109 excludes both manually hidden rows and rows hidden by a filter.
With the same amounts in B2:B7, if the South rows are filtered out and one visible North row is also manually hidden, =SUBTOTAL(9,B2:B7) still includes that manually hidden amount. =SUBTOTAL(109,B2:B7) omits it as well, so it totals only the North amounts still visible on screen. Use 109 when “visible” should mean literally visible, whether a filter or manual hiding removed the row.
The 9 and 109 codes are part of SUBTOTAL’s documented function-number choices, not separate functions. Other choices calculate operations such as AVERAGE or COUNT. In general, numbers 1 through 11 include manually hidden rows, while 101 through 111 exclude them; filtered-out rows are excluded in either group.
Apply a filter and add the total
In Excel for Microsoft 365 on Windows, you can filter a normal range and then place SUBTOTAL in a cell outside it:
- Make sure the list has one header row and no completely blank rows or columns splitting the data.
- Select a cell in the list, then choose Data > Filter. Filter arrows appear in the header cells.
- Open the arrow for the field you want to filter and select the values or conditions to display.
- In an empty cell outside the filtered rows, enter a SUBTOTAL formula using the full data range for the column you want to add.
- Change or clear the filter and check that the total updates to match the rows displayed.
For instance, with region names in A2:A7 and amounts in B2:B7, filter column A and total B2:B7. The filter can use one field or several; SUBTOTAL responds to the rows that remain visible after all active filters. The same basic Data > Filter workflow is available in Excel for Mac and Excel for the web, though ribbon layout can vary slightly by platform.
Use a table’s Total Row
An Excel table is a convenient option for lists that are likely to grow. Select a cell in the data and choose Insert > Table, confirming that the table has headers. In Microsoft 365 for Windows, click inside the table and choose Table Design > Total Row. On Mac, the Table Design tab appears when the table is selected; Excel for the web also provides table options, though the ribbon may be arranged differently.
The Total Row adds a summary row that stays associated with the table. Open the total cell’s dropdown and select Sum for the column. Excel uses a SUBTOTAL calculation so the displayed total responds to table filters. This avoids manually changing a range when new records are added. You can also select a different summary operation from the dropdown, such as Average or Count.
If you want to enter the formula yourself, with a table named Sales and a numeric column named Amount, use =SUBTOTAL(109,Sales[Amount]). This structured reference refers to the table’s Amount column and generally adjusts as table records are added or removed. To check or change a table’s name, select a cell in it and look for the table name on the Table Design tab.
What happens with multiple filters and hidden rows
SUBTOTAL follows the visible rows in the referenced range, not the meaning of a particular filter. If you filter by Region and then add a condition on Date or Status, it sums only records that remain visible after both conditions apply. There is no need to build the filter criteria into the formula itself. Change the filter selections and Excel recalculates the result.
Manual hiding is different from filtering. If you hide a row with a right-click and choose Hide, use 109 when that row should be left out; 9 continues to include it. If you clear a filter, rows hidden by that filter reappear and are included again. These distinctions matter when a workbook combines filtered data with rows intentionally hidden for presentation.
SUBTOTAL ignores other SUBTOTAL formulas within its reference. This helps prevent a grand total from counting subtotals again when a list contains intermediate summary rows. It does not mean the function can identify every kind of unwanted entry: text in a sum range is not added, while numbers stored as text may need conversion before they behave like numeric values.
Common problems and quick checks
- The answer does not change after filtering: Check that the formula begins with SUBTOTAL rather than SUM and that the referenced range covers the filtered records.
- The total is too high after hiding a row: If the row was hidden manually rather than by the filter, switch from 9 to 109 if you want it excluded.
- The result is zero or unexpectedly small: Confirm that the cells contain numeric values. Numbers imported as text may not be added as expected; convert them to numbers and check for spaces or other unwanted characters.
- The formula includes the total cell: Keep the result outside the range it totals. Including a SUBTOTAL within its own range can create confusing results, even though SUBTOTAL ignores nested SUBTOTAL formulas.
- The result differs from a visible hand calculation: Check every active filter, including filters on other columns, and make sure the selected range starts and ends on the intended data rows.
Also distinguish filtering from sorting. Sorting rearranges the rows but does not hide any, so SUBTOTAL still adds the entire referenced range. If you have filtered data but the total seems stale, Excel may be set to manual calculation; choose Formulas > Calculation Options > Automatic to restore automatic recalculation.
When to use SUMIFS instead
SUMIFS is useful when the total should be based on specified criteria, regardless of which rows happen to be visible. For example, with regions in A2:A7 and amounts in B2:B7 as in the earlier example, =SUMIFS(B2:B7,A2:A7,"North") returns 470 for North. It continues to calculate North’s total even if the worksheet is filtered to show only one of those records.
Choose SUBTOTAL when the question is “What is the sum of the rows currently visible?” Choose SUMIFS when the question is “What is the sum of rows that meet these criteria?” A filtered view and a criteria-based total can match, but they are not interchangeable: SUMIFS does not automatically respect rows hidden by filters. You can use both in a report if you want a visible-row total alongside a fixed category total.
Frequently Asked Questions
Does SUBTOTAL ignore rows hidden by a filter?
Yes. Both =SUBTOTAL(9,B2:B7) and =SUBTOTAL(109,B2:B7) exclude rows hidden by an AutoFilter. The 9-versus-109 difference applies to rows hidden manually.
Can I use SUBTOTAL with an Excel table?
Yes. Choose Sum in the table’s Total Row dropdown, or refer to the table column with a structured reference such as =SUBTOTAL(109,Sales[Amount]). The table total updates with filtering and accommodates table changes.
Why does my total include a manually hidden row?
Function number 9 includes manually hidden rows. Replace it with 109 to exclude manually hidden rows as well as rows filtered out.
Does SUBTOTAL work with more than one filter?
Yes. It calculates over the rows still visible after all active filters on the list have been applied. The formula does not need a separate argument for each filter.
Does SUMIFS total only visible rows?
No. SUMIFS evaluates its stated criteria and can include matching rows hidden by a filter. Use SUBTOTAL for a total that follows the current visible selection.
Related
- Year Calendar in Excel: Create a 12-Month View
- When Was Excel Created? The 1985 Launch Explained
- Excel Weekday Text: Show Dates as Day Names
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Data Validation Based on Another Cell: How It WorksYes. In Excel for Microsoft 365, you can make data validation depend on another cell by using a…
- Excel Double-Click Cell Shortcut: Use F2 to Edit CellsIn Excel for Microsoft 365 on Windows, double-click a cell to edit it in place; the keyboard equivalent…
- Excel Consolidate Multiple Sheets: Steps and OptionsTo consolidate multiple sheets in Excel for Microsoft 365 on Windows, go to Data > Consolidate, choose a…
- Using Solver in Excel to Find Values That Sum to a TargetTo find which numbers add up to a target in Excel, assign each number a 0-or-1 decision cell,…
- How to Insert a Fillable Checkbox in Microsoft WordTo add a fillable checkbox in Word, turn on the Developer tab, place your cursor where the box…
- Google Sheets for Expense Tracking: Setup and TipsYes—Google Sheets works well for expense tracking if you want a flexible, low-cost log you can update from…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.