To total only the rows currently visible after filtering in Excel, use =SUBTOTAL(9,B2:B6), replacing the range with your data. SUBTOTAL automatically ignores rows removed by a filter; use function number 109 instead of 9 if you also want to ignore rows hidden manually.
What SUBTOTAL does with filtered cells
Excel’s SUBTOTAL function calculates a chosen summary, such as a sum, average, or count, over one or more references. Its key advantage for filtered lists is that it recalculates using the rows left visible by the filter. You do not need to add a separate condition to the formula every time you change the filter.
This makes SUBTOTAL useful for reports where someone may filter a list by region, product, date, or another field and want a total for the displayed records. For example, a sales total can update when a user switches from viewing all regions to viewing just one.
SUBTOTAL is not the same as a regular SUM. SUM includes values in rows hidden by a filter, while SUBTOTAL is designed to omit filtered-out rows. The function does not decide which records meet a particular criterion; the filter does that. SUBTOTAL summarizes the remaining visible data.
How the function numbers work
The first argument tells SUBTOTAL which calculation to perform. Numbers 1 through 11 perform the corresponding calculation and include manually hidden rows, while still excluding rows removed by a filter. Numbers 101 through 111 perform the same calculations but also exclude manually hidden rows.
- 1 or 101: AVERAGE
- 2 or 102: COUNT, for cells containing numbers
- 3 or 103: COUNTA, for nonblank cells
- 4 or 104: MAX
- 5 or 105: MIN
- 6 or 106: PRODUCT
- 7 or 107: STDEV.S
- 8 or 108: STDEV.P
- 9 or 109: SUM
- 10 or 110: VAR.S
- 11 or 111: VAR.P
For a filtered total, 9 is the usual choice. Choose 109 when people may also hide individual rows with Excel’s Hide command or by using outline controls and those rows should not count. The difference concerns manually hidden rows; both choices ignore rows excluded by an active filter.
A worked example with a filtered sales list
Suppose A1:B6 contains headers in row 1 and five records in rows 2 through 6: North 120, South 80, East 150, West 90, and North 60. The sales amounts are in B2:B6. With that layout, enter this formula in B7:
=SUBTOTAL(9,B2:B6)
With no filter applied, the result is 500: 120 + 80 + 150 + 90 + 60. If you filter column A to show only North, the visible sales are 120 and 60, and the result becomes 180. Clear the filter and the result returns to 500. This is a practical way to make one total respond to different views of the same list.
If the East record is manually hidden while all records remain otherwise unfiltered, function number 9 still includes its 150, so the total remains 500. With the same cell layout, changing the formula to the following returns 350 because the manually hidden row is excluded:
=SUBTOTAL(109,B2:B6)
If East is hidden by a filter instead, both formulas omit that row. The remaining total is 350 in either case. This distinction explains why a formula can appear to include a hidden row: the method used to hide it matters.
Set up the filter and place the formula
In Excel for Microsoft 365 on Windows, select a cell in the data range and choose Data > Filter. Use the dropdown arrow in a header to select the values to display. Excel adds filter controls to the header row, and SUBTOTAL updates when the visible records change.
- Keep one header row above the records and avoid blank rows or columns inside the list where possible.
- Enter the SUBTOTAL formula in a cell outside the filtered records, such as directly below the amount column.
- Filter one or more columns, then check that the result reflects the visible records.
- Clear the filter from the header dropdown or use Data > Clear to restore the full list view.
Putting the result directly below the list is common, but make sure the formula cell is not itself part of the filterable data range. Otherwise it may be treated as a record or included in an unintended way. Excel tables provide a convenient alternative for keeping headers and records together, as discussed below.
SUBTOTAL with Excel tables
Formatting a range as a table can make filtered lists easier to manage. Select a cell in the data and choose Insert > Table, confirming that the table has headers if appropriate. Tables include filter dropdowns in their header row and expand when you add records.
You can turn on a Total Row from the Table Design tab. In the total cell for a numeric column, choose Sum from the dropdown. Excel uses a subtotal-based calculation for a table total, so the displayed total responds to filtering. This can be easier to maintain than placing a formula next to a changing data range.
You can also write a structured-reference formula directly. Assume the table is named Table1 and its numeric column is named Sales. In a cell outside the table, use:
=SUBTOTAL(109,Table1[Sales])
The formula returns a filtered total while also excluding manually hidden rows. If you want manually hidden records included, use 9 instead of 109. Table and column names must match the names in your workbook; Excel may adjust structured references when you rename a table or its columns.
What SUBTOTAL counts and averages
The same visible-row behavior applies when you need a count or average rather than a sum. COUNT (function number 2 or 102) counts numeric cells, so it is appropriate for counting visible sales amounts. COUNTA (3 or 103) counts nonblank cells, which can be useful for counting visible names or IDs when every record has an entry in that field.
For example, assume B2:B6 contains the five sales amounts from the worked example and B7 is outside the data range. To count visible numeric amounts, put this formula in B7:
=SUBTOTAL(2,B2:B6)
With all five records visible, the result is 5; with only the two North records visible, it is 2. For an average of the visible amounts, use function number 1 with the same range; the North-only average is 90 because (120 + 60) / 2 = 90. Empty cells and text can affect the choice between COUNT and COUNTA, so select a column whose contents match what you intend to count.
Common problems and how to diagnose them
- The total does not change: Check that the rows were excluded using Excel’s filter dropdowns, not merely hidden by another method. Also confirm that the formula’s reference covers the cells being filtered.
- A manually hidden row is still included: Change 9 to 109 for a sum, or use the matching 100-series number for another calculation.
- The result is zero or unexpectedly low: For a sum, check that the referenced cells contain numeric values rather than numbers stored as text. Confirm that the filter has not hidden every row with a numeric entry.
- The result is an error: Check the reference for an invalid range and ensure the formula was not entered inside the range it summarizes in a way that creates a circular reference.
- The result seems to double-count: SUBTOTAL ignores other SUBTOTAL formulas in its referenced range. However, unrelated formulas or values can still be counted, so inspect the referenced cells and avoid overlapping summary ranges.
SUBTOTAL is intended for vertical lists. Its 101–111 options are for excluding manually hidden rows; do not rely on them to omit hidden columns. If you need to control which columns contribute to a calculation, select the intended references explicitly.
When to use SUM, SUMIF, or another option
Use SUM when you always want every value in a range included, regardless of filters or hidden rows. Use SUBTOTAL when the answer should track the current visible view. A common misconception is that SUBTOTAL is a criteria-based function like SUMIF: it is not. It does not independently select “North” or “January”; it responds to rows made visible by filters.
If you need a result based on a condition even when the sheet’s filter changes, SUMIF or SUMIFS may be a better fit. For example, SUMIFS can total North sales whether or not North rows are currently visible. If you need a result that both follows visible rows and applies more complex conditions, the right approach depends on the workbook and may require a helper column or a more advanced formula. Choose based on whether the intended result follows the displayed records or a fixed rule.
Frequently Asked Questions
Does SUBTOTAL automatically ignore filtered rows?
Yes. SUBTOTAL excludes rows removed by an Excel filter, so the calculation updates as the filter selection changes.
Should I use 9 or 109 for a filtered sum?
Either excludes filtered-out rows. Use 9 if manually hidden rows should remain included; use 109 if they should be excluded too.
Can SUBTOTAL work with more than one filtered column?
Yes. If filters on multiple columns hide records, SUBTOTAL calculates over the records that remain visible in the referenced range.
Does SUBTOTAL ignore rows hidden manually?
Function numbers 1–11 include manually hidden rows. Their matching numbers 101–111 exclude them. Both sets ignore rows hidden by filtering.
Can I use SUBTOTAL to total only rows matching a condition?
SUBTOTAL follows the current filter; it does not specify a condition of its own. Use SUMIF or SUMIFS for totals based on fixed criteria that should not depend on which rows are visible.
Related
- Hide a Column in Power BI: Steps and Important Limits
- Bulls Eye 1-2-3 Data Sheet: Specs, Uses and Safety
- Y Balance Score: How to Calculate and Interpret It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MID Function: Can It Return Variable-Length Text?Yes. Excel’s MID function can return a variable number of characters when you calculate its num_chars argument from…
- IF Statements for Dates in Excel: Examples and RulesTo write an IF statement for dates in Excel, compare a cell containing a real date with a…
- Excel LAMBDA Examples for Reusable Custom FunctionsExcel’s LAMBDA function lets you turn a formula into a reusable custom function. For example, a definition with…
- C++ String Library Functions and How to Use ThemC++ string library functions are the standard tools for creating, measuring, changing, searching, comparing, and converting text. Most…
- How to Use the PMT Function in Excel for PaymentsUse Excel’s PMT function to calculate a fixed payment for a loan or other payment plan with a…
- Excel Split Function: How to Split Text into ColumnsIn Excel for Microsoft 365, the function for splitting a text string is TEXTSPLIT. It separates text at…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.