To total only the rows currently visible after filtering, use Excel’s SUBTOTAL function: if your amounts are in D2:D100, enter =SUBTOTAL(9,D2:D100) in a cell outside that range. SUBTOTAL recalculates as you change the filter, so the result reflects the visible records rather than every record in the list.
What SUBTOTAL Does With Filtered Rows
SUBTOTAL is designed for calculations on lists and columns of data. Its key difference from a basic SUM is that it automatically excludes rows removed from view by an AutoFilter. If you filter a sales list to show one region, a SUBTOTAL over the sales column calculates from the displayed records. Clear the filter, and the result updates to include all records again.
This behavior makes the function useful for reports where people need to explore categories without editing the calculation each time. The same formula can respond to a filter for a region, date range, product, or any other field in the list. The formula does not decide which records qualify; the filter does. SUBTOTAL simply calculates over the rows left visible.
Filtering hides records from the current view; it does not delete them or change their underlying values. The formula is therefore a view-sensitive summary, not a permanent total of a selected subset. If you copy the visible results elsewhere as values, that copy will not update when the filter changes.
Set Up a Filtered List
For reliable results, keep the data in a consistent table-like layout: one header row, one record per row, and a field in each column. Avoid blank rows splitting the data, merged cells in the header, or extra notes inside the list. These practices help Excel recognize the full range when you turn on filtering.
- Click a cell inside the list. If the range is already an Excel table, its header filters may already be available.
- On the Windows ribbon, choose Data > Filter. Excel adds a dropdown to each header in a regular range. On Mac, use Data > Filter as well.
- Use a header dropdown to select the values or conditions you want to display. The rows that do not match are hidden by the filter.
- Enter the SUBTOTAL formula in a blank cell outside the filtered data range, then change the filter to confirm that the result changes as expected.
For example, suppose a worksheet has Region in column A, Product in column B, and Sales in column D, with headers in row 1 and records in rows 2 through 5. If the sales values are 100, 200, 300, and 400, filtering Region to show only East records in rows 2 and 4 leaves sales of 100 and 300 visible. With those amounts in D2:D5, the formula =SUBTOTAL(9,D2:D5) returns 400. If you clear the filter, it returns 1,000.
Choose the Right Function Number
The first argument in SUBTOTAL tells Excel what calculation to perform. For a sum, use 9. Other common choices include 1 for average, 2 for counting numeric cells, 3 for counting nonblank cells, 4 for the largest value, and 5 for the smallest value. The reference argument is the range to calculate.
Each calculation has a second function number in the 100 series: 101 is average, 102 counts numeric cells, 103 counts nonblank cells, 104 finds the largest value, 105 finds the smallest, and 109 sums. Both versions ignore rows excluded by a filter. The distinction is how they treat rows hidden manually, such as with the Hide command or an outline.
- Numbers 1 through 11 include manually hidden rows in the calculation.
- Numbers 101 through 111 ignore manually hidden rows as well as filtered-out rows.
- For a simple filtered total, 9 is often sufficient. Choose 109 if hidden rows should be left out too.
For instance, if amounts are in D2:D100 and you want a sum that excludes both filtered-out rows and rows hidden manually, use =SUBTOTAL(109,D2:D100). The two choices can produce different results when someone manually hides a row that still belongs to the range. They do not differ merely because an AutoFilter is active: both choices ignore rows filtered out of view.
Useful Examples Beyond a Sum
SUBTOTAL can provide a visible-row average, count, minimum, or maximum as well as a total. Select a calculation based on what the values represent. For example, a count of numeric entries can help show how many transactions remain visible, while an average can summarize the records currently selected.
If the numeric sales values are in D2:D5, including 100, 200, 300, and 400, and a filter leaves only the first and third records visible, =SUBTOTAL(1,D2:D5) returns 200. It averages the two visible numbers, 100 and 300. With the same range and filter, =SUBTOTAL(102,D2:D5) returns 2 because it counts visible numeric cells. These results are useful checks when a total alone does not explain what is in the filtered view.
COUNTA-style counting has a different purpose from numeric counting. If the records have an ID in A2:A5 and each visible record has an ID, then =SUBTOTAL(103,A2:A5) counts the visible nonblank IDs. It can be a practical record count even when some sales cells are blank. Make sure the chosen column is populated for every record you intend to count; otherwise, the result counts nonblank cells, not rows in the abstract.
Using SUBTOTAL in an Excel Table
Excel tables make filtered lists easier to manage. Select a cell in the data and choose Insert > Table, confirm that the data has headers, and use the dropdowns in the table header to filter. Tables generally expand when you add records, and formulas referring to a table column can include newly added data without manually changing a range endpoint.
Assume your table is named SalesTable and its sales column is named Amount. In a cell outside the table, use =SUBTOTAL(109,SalesTable[Amount]) to sum the visible amounts while ignoring manually hidden rows too. The structured reference identifies the table’s Amount data column. To check or change the table name, select a cell in the table and look at the Table Design tab on the Windows ribbon. The tab appears when a table cell is selected.
A table can also display its own total row. Select a cell in the table, choose Table Design > Total Row, and use the dropdown in the total cell to select a calculation. Excel uses SUBTOTAL-style behavior for table totals so the result responds to filtering. The total row is convenient when the summary belongs directly under the data; a separate formula cell can be better when a report needs a fixed layout or several summaries.
Common Problems and How to Fix Them
- The result includes rows you expected to exclude. Check that a filter is actually applied and that the formula refers to the data range you are filtering. If the intended total should also omit manually hidden rows, choose the 109 version for a sum.
- The result is zero or lower than expected. Confirm that the referenced cells contain numbers rather than numeric-looking text, and check whether the filter leaves any matching records visible. Also verify that the formula’s range covers the right column and rows.
- The result changes when you hide a row manually. That is expected with the 1–11 function numbers because they include manually hidden rows. Use the matching 101–111 number if those rows should be ignored.
- The formula gives a circular-reference warning. Do not place the formula inside the range it summarizes. Put it above or below the list, or in a separate report area. A formula included in its own reference can refer back to itself.
- The total seems to count a subtotal twice. SUBTOTAL ignores other SUBTOTAL formulas inside its reference range. This helps when a list contains intermediate SUBTOTAL calculations, but it also means a nested subtotal is not treated like an ordinary number by an outer SUBTOTAL.
- New records are missing. A fixed reference such as D2:D100 does not automatically extend to row 101. Convert the range to a table or update the reference so it includes the added records.
Also distinguish a filtered row from a deleted row. A filtered record still exists in the worksheet and can reappear when you clear the filter. SUBTOTAL omits it while it is filtered out, but a regular SUM generally continues to include its value.
SUBTOTAL Compared With SUM and SUMIF
SUM is the straightforward choice when a calculation should always include every value in a range, regardless of what a filter displays. SUBTOTAL is the better fit when the summary should follow the visible rows. That difference matters in interactive lists: a SUM can keep showing the full-list total while the user is looking at only one category, which may be confusing if the label does not make the scope clear.
SUMIF and SUMIFS answer a different question. They calculate values matching criteria you specify in the formula, such as a particular region or date condition. They do not automatically change their calculation just because a user filters the source list. Use them when the calculation needs defined criteria independent of the worksheet’s current view; use SUBTOTAL when the displayed filter selection should determine the result.
In a report that needs both perspectives, it can be useful to label each result clearly: one as the all-record total and another as the visible-row total. That prevents readers from mistaking a changing filtered summary for the complete dataset total.
Frequently Asked Questions
Does SUBTOTAL ignore filtered rows automatically?
Yes. SUBTOTAL excludes rows hidden by an AutoFilter, regardless of whether you use a function number from 1–11 or the corresponding number from 101–111.
What is the SUBTOTAL formula for a filtered sum?
If the numbers to add are in D2:D100, use =SUBTOTAL(9,D2:D100). Use 109 instead of 9 if manually hidden rows should also be excluded.
Why is SUBTOTAL better than SUM with a filter?
SUBTOTAL responds to filtered-out rows, while SUM normally includes values in those rows. If you need the displayed selection to control the summary, SUBTOTAL is usually the appropriate function.
Can SUBTOTAL count visible rows?
Yes, if you choose a column that has a value on every record. With record IDs in A2:A100, =SUBTOTAL(103,A2:A100) counts visible nonblank IDs. It counts populated cells, so blanks in that column affect the result.
Does SUBTOTAL work with Excel tables?
Yes. It can refer to a table column, and a table’s Total Row provides built-in summary choices that respond to filtering. A formula outside the table is useful when you want the summary elsewhere on the worksheet.
Will SUBTOTAL update when I clear a filter?
Yes. Clearing the filter makes those records visible again, and the result recalculates to include them. SUBTOTAL is not a saved snapshot of the records that were visible earlier.
Related
- Quick Forecast in Excel: Steps, Limits, and Practical Tips
- Excel Redo Shortcut: The Quick Key and When It Works
- Punch List Checklist Template for Excel: Setup and Use
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MATCH Arguments: What Each One DoesExcel’s MATCH function has three arguments: MATCH(lookup_value, lookup_array, [match_type]). The first two say what to look for and…
- Data Validation List Formula: Excel Examples and SetupIn Excel, a data validation list formula goes in Data > Data Validation > Settings > Allow: List…
- How to Add a Formula in Excel for Addition: Simple StepsTo add numbers in Excel, select the cell where you want the answer, type a formula that starts…
- Average Between Two Dates in Excel: Formulas & TipsTo average values whose dates fall between two dates in Excel, use =AVERAGEIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<="&F2). This averages numbers in B2:B100…
- PMT Function Syntax in Excel: Arguments and ExamplesIn Excel, the PMT function syntax is =PMT(rate, nper, pv, fv, type). The first three arguments are required;…
- Excel Max IF Multiple Criteria: Formula & ExamplesThe fastest way to find a maximum value in Excel that meets multiple criteria is to use the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.