To total values that remain visible after applying Excel’s built-in column filter, use SUBTOTAL on the original data range. With sales in B2:B100, enter =SUBTOTAL(9,B2:B100); Excel recalculates the total as filter choices change. The FILTER function is different: use it to create a criteria-based subset, then use SUM to total that result.
Two meanings of “filter” in Excel
The question often comes down to two features that sound alike but work differently. The Filter command hides rows in a worksheet according to selections in column drop-downs. The FILTER function, available in Microsoft 365 and Excel 2021 or later, returns matching values as a dynamic array in another part of the sheet. One changes which source rows are displayed; the other produces a formula result.
SUBTOTAL is built to calculate over a reference while accounting for rows excluded by an AutoFilter. FILTER evaluates a condition and returns matching data. So, if your goal is a total that responds to the worksheet’s column filter, use SUBTOTAL on the source range. If your goal is a total for a condition such as one region or product, use SUM(FILTER(...)). They can support related tasks, but they do not mean the same thing.
How SUBTOTAL responds to hidden rows
The first argument of SUBTOTAL selects a calculation. Function number 9 means SUM, so SUBTOTAL(9,reference) adds values in the reference. When a worksheet AutoFilter hides rows, SUBTOTAL excludes those rows from the calculation. Change the filter and the displayed total updates without changing the formula.
There is a useful distinction between function numbers 9 and 109. Both sum visible rows and ignore rows excluded by a filter. The 9 version includes rows you hide manually, while 109 excludes manually hidden rows as well. For a table where both kinds of hiding might matter, use 109. For rows hidden only by the AutoFilter, either number gives the same result.
For example, with sales values in B2:B100, use =SUBTOTAL(109,B2:B100) if you want the sum to ignore both filtered-out and manually hidden rows. If the cells contain 1,200, 800, and 500 in the three rows that remain visible, the result is 2,500. Rows excluded by the filter do not contribute, even if they contain numbers.
Set up a total that follows an AutoFilter
For a straightforward worksheet, arrange headings in the first row and data underneath. Select a cell in the data range, then choose Data > Filter. Use the drop-down in a heading to select the rows to display. Put the subtotal formula in a cell outside the filtered list—above or below it, for example—so the result does not become part of the records being filtered.
With item names in A2:A100 and sales amounts in B2:B100, enter =SUBTOTAL(9,B2:B100) in a separate cell. If the filter is set to show only East region records, the result is the sum of the visible East sales, assuming the region field is included in the filtered range. Clear the filter and the formula returns to the sum of all included rows.
Excel tables make this easier to maintain because structured references expand as records are added. If the table is named SalesTable and its amount column is named Sales, place =SUBTOTAL(109,SalesTable[Sales]) outside the table. The table’s built-in filter buttons can hide records, and the subtotal responds. To create a table, select the data and choose Insert > Table; confirm that the range has headers if it does.
Use FILTER for a criteria-based total
Use FILTER when you want a formula to return records matching specified conditions, rather than using the worksheet’s filter drop-downs. The function takes an array to return, a condition array that identifies matching rows, and an optional result for the case where no rows match. Its results spill into neighboring cells, so keep the spill area clear.
With regions in A2:A100 and sales in B2:B100, use =SUM(FILTER(B2:B100,A2:A100="East",0)) to total East sales. If the matching sales are 1,200 and 800, the formula returns 2,000. The final argument, 0, means the formula supplies zero when there are no matches, rather than returning a no-results error from FILTER. This result is based on the condition in the formula; it does not mean that SUBTOTAL is monitoring the worksheet’s AutoFilter.
You can combine conditions by multiplying logical tests, which requires both tests to be true. With regions in A2:A100, product names in B2:B100, and sales in C2:C100, enter =SUM(FILTER(C2:C100,(A2:A100="East")*(B2:B100="Widget"),0)) to sum Widget sales in the East region. Each range must cover the same rows so the test results line up with the values being returned.
Can you combine SUBTOTAL and FILTER?
It is tempting to put FILTER directly inside SUBTOTAL. That is usually not the right approach when the aim is to make a total track the source table’s AutoFilter: FILTER returns an array of matching values, whereas SUBTOTAL’s special handling is tied to a worksheet reference and its row visibility. The two functions do not automatically share the same filtering state.
Choose the formula based on what should control the result. For the current visible rows in the original list, place SUBTOTAL over the source column. For a formula-defined condition, use SUM around FILTER. If FILTER spills a list and you later want to total that displayed list, SUM can total the spill range. With a FILTER result beginning in D2, =SUM(D2#) adds the spilled values. The # notation refers to the entire spill range, which can grow or shrink when the criteria change.
That spill-range total reflects the values returned by FILTER; it is not a substitute for a subtotal that follows a separate AutoFilter applied to the original records. Keeping that distinction clear prevents totals that appear plausible but respond to a different set of conditions than expected.
Common problems and how to fix them
- The total does not change when rows are hidden. Check that the rows were excluded by an AutoFilter and that the formula references the affected records. A normal SUM includes values in hidden rows, but SUBTOTAL is designed to respond to filtered-out rows.
- Manually hidden rows still count. If you used function number 9, this is expected. Change it to 109 when manually hidden rows should also be excluded.
- The formula returns zero. Verify that the chosen column contains numeric values, that the filter has not removed every record, and that the formula points to the intended rows. Numbers stored as text may not add as expected.
- FILTER returns an error when nothing matches. Supply its optional if_empty argument, such as 0 in the examples above, and confirm that the condition matches the data exactly. Extra spaces or inconsistent labels can cause a condition to find no records.
- The FILTER result shows #SPILL!. Clear cells blocking the output area. A dynamic array needs room to return all matching values; a cell containing even a space can obstruct the spill.
- The total includes the formula itself or unexpected rows. Keep the subtotal outside the filtered list, and check the start and end of its reference. A subtotal placed inside a data range can also complicate filtering and analysis.
Which method should you choose?
The deciding question is what should determine which records count. If a person will use the column drop-downs to explore the same list, SUBTOTAL is usually the simplest option. It responds immediately to the currently visible records and does not require a separate criteria formula.
If the desired selection is part of a report—for example, always show and total one region—FILTER is useful because the criteria are visible in the formula and the returned records can feed other calculations. It also keeps the source list intact while presenting a separate result. For a fixed condition where only a total is needed, SUMIFS may be a more direct alternative: it calculates a conditional sum without spilling a list. It does not, by itself, behave like a visible-row subtotal when a user changes an AutoFilter.
For a repeatable summary across several categories, a PivotTable may be easier to maintain than a long set of formulas. PivotTables let users rearrange and summarize fields interactively. SUBTOTAL is handy for a single total that follows filtered rows; FILTER is handy for a formula-generated extract; and SUMIFS or a PivotTable may suit a criteria-based summary better.
Frequently Asked Questions
Does SUBTOTAL ignore filtered rows in Excel?
Yes. SUBTOTAL excludes rows hidden by an AutoFilter. Use function number 9 to sum while including manually hidden rows, or 109 to exclude manually hidden rows too.
Can I use SUBTOTAL with Excel’s FILTER function?
You can use both in a workbook, but they solve different problems. Use SUBTOTAL on the original range for a total that responds to worksheet filtering. For a condition-based FILTER result, use SUM(FILTER(…)) to calculate its total.
Why does SUBTOTAL include a row I hid?
Function number 9 includes manually hidden rows. Use 109 if manually hidden rows should be ignored. Rows removed by an AutoFilter are excluded with either number.
Does FILTER change the source data or hide rows?
No. FILTER returns matching values in a separate spill range and leaves the source records in place. The worksheet’s Data > Filter command instead hides nonmatching rows in the original list.
Is FILTER available in every Excel version?
No. FILTER is available in Microsoft 365 and Excel 2021 or later. In an older version without the function, use AutoFilter with SUBTOTAL for visible-row totals, or consider SUMIFS for conditional totals.
Related
- Excel: Find Where a Cell Is Used Across Sheets
- Excel Custom Number Format for Millions
- Excel Custom Number Format: Show Numbers in Thousands
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Split Cells in Excel: Text to Columns and FormulasTo split the contents of a cell in Excel, select the cells, then choose Data > Text to…
- Excel SUMIF by Text Color: What Works and What Doesn’tExcel’s SUMIF function cannot directly test a cell’s text (font) color. To total values by color, use a…
- Excel Test If a Cell Is Empty: Formulas and ExamplesTo test whether a cell is genuinely empty in Excel, use =ISBLANK(A1), replacing A1 with the cell you…
- Excel Formula for Hours Worked Per Day With BreaksTo calculate hours worked per day in Excel, enter the start time in B2, end time in C2,…
- Gtt per Min Formula: Examples and Safe CalculationThe gtt per min formula is gtt/min = (mL × drop factor in gtt/mL) ÷ time in minutes.…
- Excel PV of Future Value: Formula and ExamplesTo find the present value of a future amount in Excel, use the PV function with no periodic…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.