To total a filtered list in Excel, place the result outside the list and use =SUBTOTAL(9,B2:B100), assuming the values you want to total are in B2:B100. SUBTOTAL recalculates when you filter rows, and its function number lets you calculate other results such as an average or count. The steps below are for Excel for Microsoft 365 on Windows.
What You’ll Need
- An Excel worksheet with a list of records, ideally with a single header row and consistent columns.
- A column of numbers for a sum, average, or other numeric calculation. For a count of nonblank cells, the column can contain text or numbers.
- A clear cell outside the range you plan to subtotal, so the result does not become part of its own calculation.
You do not need to convert the range to a table, but it should have no completely blank rows interrupting the data. If you want to filter records, turn on filters with Data > Filter or select a cell in the list and press Ctrl+Shift+L on Windows. On Mac, the shortcut is Command+Shift+F. Excel for the web also has the Filter command on the Data tab, though shortcut availability can vary by browser.
Step-by-Step
- Arrange the list. Put one field name in each header cell, then keep each record on one row. For example, a simple sales list might have Region in column A and Sales in column B, with headers in row 1 and records in rows 2 through 6. Avoid merged cells in the data area; they can make filtering and selecting the range unreliable.
- Decide which result you need. The first number inside SUBTOTAL specifies the calculation. Use 9 for SUM, 1 for AVERAGE, 2 for COUNT of numeric cells, 3 for COUNTA of nonblank cells, 4 for MAX, or 5 for MIN. SUBTOTAL also supports product, standard deviation, and variance calculations. The number is not a cell reference; it is a code that tells Excel what to calculate.
- Select an output cell outside the list. Click a blank cell below the values or in a summary area to the side. If the Sales values are in B2:B6, do not put the result in B2:B6. Putting it in B7 is suitable if row 7 is currently clear and is not included in the data range. Keeping the result outside the range prevents circular references and makes the summary easy to find.
- Enter the formula for the calculation. With sales values in B2:B6 and the formula in B7, use
=SUBTOTAL(9,B2:B6). If those values are 120, 80, 150, 90, and 60, the result is 500. Press Enter to calculate it. In Excel for Microsoft 365, formulas use commas between arguments in the examples here; some regional settings use semicolons instead. - Turn on filtering if needed. Click a cell in the list, then choose Data > Filter. Use the arrow in the Region header to select the region or records you want to view. The SUBTOTAL formula updates to include the visible records and ignore rows hidden by the filter. For example, if the visible Sales values are 120, 150, and 60, the sum becomes 330. Clear the filter to bring the result back to 500.
- Choose how manually hidden rows should behave. Function numbers 1 through 11 ignore rows hidden by a filter, but still include rows you hide manually. Numbers 101 through 111 ignore both filtered-out rows and manually hidden rows. If you want a sum that excludes manually hidden rows as well, with sales in B2:B6 and the result outside that range, use
=SUBTOTAL(109,B2:B6). For the same five sales values, it returns 500 while all rows are visible; if you manually hide the row containing 80, it returns 420. - Use the same range for other calculations. With the numeric sales data in B2:B6,
=SUBTOTAL(1,B2:B6)returns the average, 100, while all five values are visible. With names or other entries in A2:A6,=SUBTOTAL(3,A2:A6)counts nonblank visible entries. The second example assumes A2:A6 contains one nonblank entry for each record. When you filter the list, each formula recalculates using the remaining visible rows. - Check the result after changing the list. Filter the records, clear the filter, and confirm that the total changes as expected. If you will add records later, allow for those rows in the formula range or use an Excel table so the data range can expand more reliably. Keep the output cell outside the range being subtotaled.
The general pattern is a function number followed by one or more references. You can include separate ranges as additional arguments when needed. The selected calculation applies to every referenced range, so check that all ranges contain the type of data you intend to summarize.
Common Mistakes
- Using the wrong function number. A 9 calculates a sum; a 1 calculates an average. If the result is a total but you expected an average, check the first argument before changing the range. Numbers 101 through 111 select the corresponding calculations while also excluding manually hidden rows.
- Expecting function number 9 to ignore manually hidden rows. It ignores filtered-out records, but includes rows hidden manually. Use 109 for a sum that should leave out both types of hidden rows. This distinction matters if you hide rows to focus on a subset without applying a filter.
- Including the result cell in the reference. If the formula is entered within the range it totals, Excel may report a circular reference or produce an unreliable result. Move the formula to an outside cell and verify the referenced range.
- Assuming SUBTOTAL adds the results of multiple subtotals. When a SUBTOTAL range contains other SUBTOTAL formulas, Excel ignores those nested subtotal results to avoid counting them twice. That is useful for grouped summaries, but can surprise you if you expected a grand total to add the displayed subtotal cells. Reference the underlying data or choose a layout that matches the calculation you need.
- Using COUNT when you mean COUNTA. Code 2 counts numeric cells only. Code 3 counts nonblank cells, including text. If your column contains names or category labels, code 2 may return zero even though the cells are filled.
- Using inconsistent or interrupted data. Blank rows, text mixed into a numeric column, and accidental spaces can make the range harder to filter or interpret. A sum ignores text in referenced cells, which may hide data-entry problems rather than flag them.
Tips
For a list that you filter often, keep the header row clear and use Data > Filter rather than hiding rows one by one. Filtering is the main reason SUBTOTAL is useful: the formula responds to which records are visible without needing a new formula for each selection. If other people will use the workbook, label the result cell, such as “Visible sales total,” so its behavior is clear.
Consider converting a well-formed range into a table by selecting a cell in it and choosing Insert > Table, then confirming that the table has headers. Tables make filtering straightforward and can automatically extend as you add rows. However, formulas that use table structured references have their own syntax; a standard cell-range formula such as the examples above is often easiest when learning SUBTOTAL. Check that new records are included in the reference if you do not use a table.
Remember that filtering and hiding are different. A filter hides records that do not match the selected criteria, and every SUBTOTAL function number excludes those filtered-out rows. Manually hiding a row affects the 101–111 codes but not the 1–11 codes. Choose the code based on how you want the summary to behave, not merely on whether a row looks hidden on screen.
SUBTOTAL is not limited to totals. Use it when you need a visible-row average, minimum, maximum, or count that changes with a filter. When comparing a subtotal with a separate calculation, make sure both use the same visible records and the same treatment of manually hidden rows. This avoids mismatches caused by different ranges or function numbers.
Excel for Mac uses the same SUBTOTAL function and function numbers. The ribbon layout can differ slightly by version, but filtering is available from the Data tab. In Excel for the web, the formula and filter behavior are generally the same; if a keyboard shortcut does not work in your browser, use the ribbon command instead.
Troubleshooting
The result does not change when you filter. Check that the filter applies to the rows included in the formula, and that the formula refers to the data column rather than a fixed value or a different range. Clear the filter and apply it again to the complete list if some rows were left outside the filter area. Also confirm that the displayed result is a SUBTOTAL formula, not a typed number.
The total is too high after hiding rows. If you used 9, manually hidden rows are still included. Change to the matching code in the 101–111 range, such as 109 for SUM, if manually hidden rows should be excluded too. Filtered-out rows are excluded with either code family.
The result is zero or unexpectedly small. Check whether the values are real numbers or text that only looks like numbers. Select a suspect cell and inspect the formula bar, or try changing the cell format and re-entering the value. Confirm that the reference includes the intended rows and that the chosen code matches the data type: COUNT ignores text, whereas COUNTA counts nonblank text entries.
Excel shows a formula error or does not accept the formula. Check that the function name is spelled SUBTOTAL and that the parentheses are balanced. In some regional configurations, Excel expects semicolons in place of commas; if the formula is rejected, use the separator expected by your installation. If you see a circular-reference warning, move the formula cell outside its own referenced range.
The total differs from a calculator. Check for filtered-out records, manually hidden rows, and numbers stored as text. Also look for nested subtotals: SUBTOTAL deliberately ignores other SUBTOTAL results within the referenced range. Compare the formula’s precise range with the rows you added manually, rather than relying only on the visible extent of the list.
Frequently Asked Questions
What is the difference between SUBTOTAL and SUM?
SUM adds values in its range regardless of whether rows are hidden by a filter. SUBTOTAL can ignore filtered-out rows and offers several calculations besides addition, so it is usually more suitable for summaries of filtered lists.
Which SUBTOTAL number should I use for a total?
Use 9 for a sum that ignores filtered-out rows but includes manually hidden rows. Use 109 when you also want to exclude manually hidden rows. The two codes return the same total when no rows are manually hidden.
Can SUBTOTAL calculate an average or count?
Yes. Use function number 1 for AVERAGE, 2 for COUNT of numeric cells, and 3 for COUNTA of nonblank cells. Use the corresponding codes 101, 102, or 103 if manually hidden rows should be excluded too.
Does SUBTOTAL work with Excel filters?
Yes. When you use a filter on the rows in its reference, SUBTOTAL updates to calculate over the visible records. It can be used with a filtered range or an Excel table, as long as the referenced data matches the list.
Can I put more than one range in a SUBTOTAL formula?
Yes. SUBTOTAL accepts multiple references, separated by commas in the examples here. Confirm that every range belongs in the same calculation and that you have not included the output cell in one of them.
Why does my grand total skip subtotal rows?
Excel ignores nested SUBTOTAL formulas included within another SUBTOTAL’s referenced range. This prevents subtotal values from being counted twice. If the skipped rows are ordinary values rather than nested subtotal formulas, check the range and whether those rows are filtered or hidden.
Related
- Energy Audit Template in Excel: What to Include
- How to Indent Text in an Excel Cell
- Encrypt an Excel File with a Password: Steps and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- MySQL String Functions: Examples, Uses, and Common IssuesMySQL string functions operate on text values to join, inspect, extract, search, and modify strings. Common examples include…
- C String Functions: What They Do and How to Use ThemC string functions are library routines for measuring, copying, joining, comparing, searching, and splitting null-terminated character sequences; most…
- XLOOKUP Case Sensitivity: How Excel Matches Text ValuesNo. In Excel for Microsoft 365, XLOOKUP is not case-sensitive: it treats text such as “Maple,” “MAPLE,” and…
- Round in Excel to a Whole Number: Formula and ExamplesTo round a number to the nearest whole number in Excel, use ROUND with 0 as the number…
- Excel Left of a Specific Character: How ToTo get text to the left of a specific character in Excel, use the LEFT function combined with…
- Excel XLOOKUP Trim: How to Remove SpacesTo trim spaces inside an XLOOKUP, wrap the lookup value and the lookup array in the TRIM function:…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.