In Excel for Microsoft 365 on Windows, use SUBTOTAL to calculate a sum, average, count, or other summary that updates when you filter a list. With amounts in B2:B10, enter =SUBTOTAL(9,B2:B10) to sum visible values; use function number 109 instead of 9 if you also want to exclude rows you hide manually. You can also use Excel’s Data > Subtotal command to insert group totals into a sorted list.
What You’ll Need
- An Excel workbook with a rectangular list, including a header row and one record per row.
- A column of numeric values for calculations such as sum or average, and a field to group by if you want subtotals for categories.
- For the Data > Subtotal command, use the desktop version of Excel. The command may not be available in Excel for the web; the
SUBTOTALworksheet function works in supported web workbooks.
For the examples below, imagine a sales list with categories in column A, item names in column B, and sales amounts in column C. The data begins in row 2, beneath headers in row 1. Adjust the columns and row numbers to match your own sheet.
Step-by-Step
- Choose whether you need a formula or inserted group totals. Use the function when you want a summary cell that responds to filters, or when you want a calculation in a dashboard or report. Use the Subtotal command when you want Excel to add a subtotal row after each category in a list, plus an overall total and collapsible outline controls. The two approaches solve related but different tasks.
- Check and prepare the list. Make sure each column has a clear header, records are in one continuous range, and there are no completely blank rows splitting the list. If you plan to use the command to subtotal by category, sort the data by that category first. In Excel, select a cell in the list, then choose Data > Sort and select the category column. Sorting keeps each category’s records together so Excel can put one subtotal after each group. If the list is formatted as an Excel table, the Subtotal command may be unavailable; use Table Design > Convert to Range if you specifically need that command. Converting changes the table into an ordinary range.
- Decide which rows the calculation should include. A normal SUBTOTAL calculation excludes rows removed by a filter. For manually hidden rows, select a function number from 101 through 111 to exclude them too. The number controls the calculation: 9 means SUM, 1 means AVERAGE, 2 means COUNT, and 3 means COUNTA. COUNT counts numeric cells; COUNTA counts non-empty cells. Other options include MAX, MIN, PRODUCT, standard deviation, and variance. The second argument is the range to evaluate.
- Enter a basic sum formula. With sales amounts in C2:C11 and the result placed in C12, enter
=SUBTOTAL(9,C2:C11)in C12. The first argument, 9, selects SUM; the range tells Excel which amounts to add. If the values are 120, 80, 150, 50, and 100, with the other cells blank, the result is 500. Replace the example range with the full range of your data, but do not include the result cell itself. - Use a filter-aware average or count if needed. With sales amounts in C2:C11, enter
=SUBTOTAL(1,C2:C11)to average the visible numeric values. With item names in B2:B11, enter=SUBTOTAL(3,B2:B11)to count visible non-empty names. Apply a filter using Data > Filter, then choose a category from the drop-down in the header. The subtotal changes to reflect the rows still visible. If you need to count numeric entries instead of names, use function number 2 and the numeric range. - Choose whether to ignore manually hidden rows. With sales amounts in C2:C11, enter
=SUBTOTAL(109,C2:C11)to sum visible values while excluding both filtered-out and manually hidden rows. In contrast, function number 9 ignores rows excluded by a filter but includes rows hidden with Hide. The equivalent pairs are 1–11 for calculations that include manually hidden rows and 101–111 for calculations that exclude them. Filtering is ignored by neither set: filtered-out rows are excluded in both. - Insert category subtotal rows with the ribbon command. First sort the list by category. Select any cell in the data, then choose Data > Subtotal in the Outline group. In the dialog, set At each change in to the category field, set Use function to Sum, and select the numeric field under Add subtotal to, such as Sales. Leave Summary below data selected if you want each group’s subtotal after its records. Select OK. Excel inserts subtotal rows and an overall total, and adds outline buttons at the left of the sheet. The exact dialog labels can vary slightly by Excel build.
- Review the result and its outline. Use the numbered outline buttons to show only the overall total, group subtotals, or all detail rows. The plus and minus controls expand or collapse individual groups. Check a few subtotal rows against the underlying records, especially after sorting or changing the selected field. To remove command-generated rows and return to the original list, select a cell in the range and choose Data > Subtotal > Remove All. This removes the inserted subtotal structure; it does not delete the original records.
- Save and test the behavior you need. If your summary is meant to respond to filtering, turn on or use the column filters and confirm the total changes as expected. If a report needs to ignore manually hidden rows, hide one data row and verify that you used a 101–111 function number. Save the workbook after checking the result. On Mac, use the Data tab and look for the Subtotal command; ribbon placement can differ. Excel for the web may not provide the command or outline controls, so use a SUBTOTAL formula there when you need a filter-responsive calculation.
Common Mistakes
- Including the result cell in its own range. A subtotal formula that sums a range containing itself can create a circular reference. Put the formula outside the data range or stop the range at the last record.
- Expecting function 9 to ignore manually hidden rows. It ignores filtered-out rows, but manually hidden rows remain included. Use 109 if hidden rows should be left out.
- Sorting only one column. This separates values from their associated records. Sort the entire list by selecting a cell within it and using the Sort command, rather than sorting an individual column by itself.
- Using the Subtotal command on an Excel table. The command can be unavailable for a table. Either use a SUBTOTAL formula or convert the table to a range first, understanding that table features such as automatic expansion and structured references will no longer apply.
- Forgetting to sort before inserting group totals. The command creates a subtotal each time the selected group field changes. If categories are scattered throughout the list, Excel may create multiple subtotals for the same category.
- Choosing the wrong count behavior. COUNT includes numeric cells only; COUNTA counts cells containing text or numbers. A count of names should generally use COUNTA on the name column, not COUNT.
Tips
- Keep the formula’s result outside the cells it summarizes. If you add new records later, expand the range or use a suitable table-based formula so the new rows are included.
- Use the same function number consistently in a report. A 9 total and a 109 total can differ when rows are manually hidden, which can make nearby summaries appear inconsistent.
- Use SUBTOTAL rather than SUM for a filter-sensitive total. SUM includes values in rows filtered out of view, while SUBTOTAL excludes those filtered rows.
- When you need several summaries, put each formula in a separate cell and point it at the appropriate range. For example, an average and a visible-item count can sit above the same filtered list without changing the source data.
- The Data > Subtotal command can create nested levels when applied more than once with Replace current subtotals cleared. Use this only when you intentionally need more than one grouping level, and sort by the outer grouping field and then the inner one.
- Remember that SUBTOTAL ignores other SUBTOTAL results within its reference. This helps prevent double-counting when a formula includes subtotal rows generated by the command.
Troubleshooting
The total does not change after filtering. Confirm that the formula uses SUBTOTAL rather than SUM, that the filter is applied to the rows in the referenced range, and that the formula’s range covers those rows. If the filtered column is outside the list’s filter range, select the full dataset and reapply Data > Filter.
The result is zero or lower than expected. Check that the cells contain real numbers rather than numbers stored as text, and verify that the chosen range and function number are correct. To see whether text is involved, select a sample cell and inspect its value and number format. A text value in a sales column will not be added by SUM.
The result includes a row you hid. Change from function number 9 to 109 for a sum, or use the corresponding 100-series number for another operation. Remember that a row hidden by filtering is excluded either way, but a row hidden manually is excluded only by the 100-series options.
The Subtotal command is disabled or the dialog groups incorrectly. If the data is an Excel table, convert it to a range or use the formula method. If Excel creates several totals for one category, sort the entire data range by the chosen category field before running the command again. Remove existing generated rows first with Data > Subtotal > Remove All if you want to rebuild the grouping.
You see a circular reference warning. Move the formula outside the range it summarizes, then adjust the range so it contains only source records. Also check whether a generated subtotal row or grand total has accidentally been included as source data.
Frequently Asked Questions
What does the number in SUBTOTAL mean?
The first argument selects the operation. For example, 9 means SUM, 1 means AVERAGE, 2 means COUNT, and 3 means COUNTA. Numbers 101 through 111 select the corresponding operation while also ignoring manually hidden rows.
Does SUBTOTAL ignore filtered rows?
Yes. Both the 1–11 and 101–111 function-number groups exclude rows hidden by a filter. Only the 101–111 group also excludes rows hidden manually.
What is the difference between SUBTOTAL and SUM?
SUM adds values in its range whether or not some records are filtered out of view. SUBTOTAL is designed for lists and excludes filtered-out rows, making it useful for totals that follow a filter.
Can I use SUBTOTAL with more than one column?
Yes, but each reference should contain the values you want to calculate together. For separate measures, such as sales and units, use a separate formula for each column so each result is easy to interpret.
Can I use SUBTOTAL in Excel for the web?
The worksheet function is supported in Excel for the web, so a filter-aware calculation can work there. The desktop Data > Subtotal command and its outline controls may not be available in the web version.
How do I remove subtotal rows?
For rows inserted by the command, select a cell in the list and choose Data > Subtotal > Remove All. This removes the generated subtotal rows and outline, while leaving the original data records in place.
Related
- X-Arcade Programming Software: What You Need to Know
- Line of Balance Schedule: How It Works on Projects
- How to Subtotal in Numbers and Summarize by Category
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Concatenation: Other Ways to Join Text ValuesBesides the ampersand (&), you can join text in Excel with CONCAT, TEXTJOIN, or the older CONCATENATE function;…
- Google Sheets FILTER Function: Practical Examples and SetupIn Google Sheets, use FILTER to show only rows that meet one or more conditions. Enter a formula…
- Cell in Excel: Meaning, References, and How It WorksA cell in Microsoft Excel is one box in a worksheet, formed where a row and a column…
- Excel Format Cells Shortcut: Quick Keys for Windows and MacIn Excel for Microsoft 365 on Windows, press Ctrl+1 to open the Format Cells dialog for the selected…
- Create a Label Template in Word: Step-by-Step GuideTo create a reusable label template in Microsoft Word, open Mailings > Labels, choose the label brand and…
- PowerPoint Slideshow Loop: How to Repeat It AutomaticallyTo keep a PowerPoint slideshow looping, open the presentation in the desktop app and choose Slide Show >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.