Excel has two things called subtotal, and people often confuse them. The SUBTOTAL function calculates a sum, average, count or other result for a list, and it can ignore rows hidden by a filter. The Subtotal command on the Data tab inserts subtotal rows into a sorted list, one for each group, with an outline that lets you collapse the groups. Both are useful for reports. This guide explains each, with a small data set that you can check by hand, lists the function numbers, shows the difference between 9 and 109, explains why SUM gives the wrong total on a filtered list, shows how to add and remove grouped subtotals, and covers the mistakes that cause double counting.
Updated November 2026
In this guide
- The sample data
- The SUBTOTAL function syntax
- Function numbers
- SUBTOTAL with a filter
- 9 versus 109
- Count visible rows and average visible values
- The Data tab Subtotal command
- Use the outline buttons
- Add more than one subtotal or nested subtotals
- Remove subtotals
- SUBTOTAL compared with SUMIFS and pivot tables
- Common problems and fixes
- Quick recap
- Report layout tip for client files
- Practice problems
- Common mistakes
The sample data
The examples use this sales list in A1:C7.
| Region | Rep | Amount |
|---|---|---|
| East | Ali | 1200 |
| East | Omar | 1500 |
| West | Sara | 800 |
| West | Bilal | 950 |
| North | Hina | 600 |
| North | Zain | 700 |
The total of all amounts is 5,750. The East total is 2,700, the West total is 1,750 and the North total is 1,300.
The SUBTOTAL function syntax
The syntax is =SUBTOTAL(function_num, ref1, [ref2], ...). The first argument is a number that tells Excel which calculation to perform, and the others are the ranges. For example, =SUBTOTAL(9,C2:C7) returns the sum, which is 5,750. The function is special because it ignores other SUBTOTAL results inside its range, which prevents double counting when you have totals within totals. It also ignores rows that are hidden by a filter, which a normal SUM does not.
Function numbers
Each calculation has two numbers. The numbers 1 to 11 include manually hidden rows, and the numbers 101 to 111 ignore them. Rows hidden by a filter are always ignored by both sets.
| Calculation | Include hidden | Ignore hidden |
|---|---|---|
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV.S | 7 | 107 |
| SUM | 9 | 109 |
SUBTOTAL with a filter
Suppose you filter the list to show only the East region. A normal =SUM(C2:C7) still returns 5,750, because SUM counts the hidden rows. =SUBTOTAL(9,C2:C7) returns 2,700, the sum of the two visible rows, 1,200 plus 1,500. Place the SUBTOTAL formula above the data or in a total row, and it updates every time you change the filter. This is the best way to show a live total on a filtered report. Turn on the filter with Ctrl+Shift+L, as explained in our guide to sorting data in Excel, and choose the region in the drop-down.
9 versus 109
The difference appears only when you hide rows by hand, using right-click and Hide. With =SUBTOTAL(9,C2:C7), a manually hidden row is still counted. With =SUBTOTAL(109,C2:C7), it is not. If you hide the Sara row manually, the 9 version still returns 5,750, while the 109 version returns 4,950. Filtered rows are excluded by both. Use 109 when users may hide rows by hand and you want the total to match what they see. Use 9 when you want hidden rows to remain in the total.
Count visible rows and average visible values
Use =SUBTOTAL(103,B2:B7) to count the visible, non-empty rows, which returns 6 with no filter and 2 when the East filter is applied. Use =SUBTOTAL(101,C2:C7) to average the visible amounts, which is 958.33 with no filter, since 5,750 divided by 6 is 958.33, and 1,350 with the East filter. Use 104 and 105 for the maximum and minimum of the visible rows. These formulas make a good header row for a report, showing the count, the total and the average of whatever the user has filtered.
The Data tab Subtotal command
The Subtotal command inserts total rows for each group in a sorted list. First sort the data by the column that defines the groups, such as Region, as shown in our sorting guide. Click a cell in the list, go to Data, and click Subtotal. In At each change in, choose Region. In Use function, choose Sum. In Add subtotal to, tick Amount. Click OK. Excel inserts a row after each group with the label East Total, West Total and North Total, and a Grand Total at the bottom, using the SUBTOTAL function with function number 9. For the sample, the totals are 2,700, 1,750 and 1,300, and the grand total is 5,750. Notice that the list is sorted by region, so each region appears in one block.
Use the outline buttons
After you add subtotals, Excel shows numbered buttons 1, 2 and 3 at the top left, and plus and minus signs along the side. Click 2 to collapse the list to the group totals and the grand total. Click 3 to show everything. Click 1 to show only the grand total. This outline view is useful for summary reports, since you can hand over a one-page view of the totals and still keep the details below. To copy only the totals, select the visible rows, press Alt+; on Windows to select visible cells only, and paste. Without this step, hidden rows would be copied too.
Add more than one subtotal or nested subtotals
To add a second level, such as a total by Region and then by Rep, sort by both columns, add the first subtotal, and then run the Subtotal command again. Choose the second column, and clear Replace current subtotals, so that both levels stay. Excel adds more outline levels. Alternatively, use a different function, such as Average or Count, in a second run to show both the total and the average in the same list. Keep the number of levels small, because a report with many nested levels is hard to read and hard to update.
Remove subtotals
To remove the subtotal rows, click a cell in the list, go to Data, then Subtotal, and click Remove All. Excel deletes the subtotal rows and the outline, and leaves your original data. If you only want to remove the outline but keep the totals, go to Data, Ungroup, and choose Clear Outline. Note that Subtotal does not work on an Excel table, so if the Subtotal button is greyed out, convert the table to a normal range with Table Design, Convert to Range, and then apply it.
SUBTOTAL compared with SUMIFS and pivot tables
SUBTOTAL is simple and works on filtered data, but it gives only one number at a time. SUMIFS gives a total for a condition, such as the East region, without filtering, as shown in our SUMIFS guide. A PivotTable gives totals for all groups at once and updates with a refresh. Use SUBTOTAL for a live total on a filtered list, SUMIFS for fixed report cells, the Subtotal command for a quick grouped printout, and a PivotTable for flexible analysis. When a client wants a one-off summary of a sorted list, the Subtotal command is often the fastest.
Common problems and fixes
If your SUBTOTAL total is not changing when you filter, you may have used SUM instead. If the subtotal is wrong after adding rows at the bottom, the range may not include the new rows, so extend the range or use a whole column reference. If numbers are stored as text, SUBTOTAL ignores them, so convert them as shown in our guide to converting text to numbers in Excel. If the Subtotal command inserts totals in strange places, the list was not sorted by the group column. If a grand total double counts, you may have put a normal SUM over cells that already contain subtotals, so use SUBTOTAL for the grand total too.
Quick recap
Use SUBTOTAL 9 or 109 for a total that follows your filter, 101 for the average of visible rows and 103 for a visible count. Choose 109 when rows may be hidden by hand. Sort by the group column before using Data, Subtotal, collapse with the outline buttons, copy only visible cells, and use Remove All to start again. For flexible analysis across all groups at once, move to a PivotTable, and for fixed report cells use SUMIFS.
Report layout tip for client files
When you hand over a filtered report, place a small summary block above the headings with the labels Visible rows, Total and Average, and use SUBTOTAL 103, 109 and 101 beside them. Freeze the rows below the block, as shown in our freeze panes guide, so that the summary stays in view while the user scrolls and filters. Label the block clearly, so the reader understands that the numbers change with the filter. This small addition makes a plain data list feel like a finished report, and it prevents the common question of why the total did not change after filtering.
Practice problems
Try these on the sample data. Problem 1: use SUBTOTAL 9 to find the total, which is 5,750. Problem 2: filter to West and check that SUBTOTAL 9 returns 1,750. Problem 3: hide the Hina row by hand, and compare SUBTOTAL 9 and 109. With no filter, 9 returns 5,750 and 109 returns 5,150. Problem 4: use the Data Subtotal command to add the Average by Region, which gives 1,350 for East, 875 for West and 650 for North. Problem 5: collapse to level 2 and copy the visible rows to a new sheet. Check each answer by adding the numbers yourself.
Common mistakes
The most common mistake is to use SUM on a filtered list and wonder why the total does not change. Another is to forget to sort before using the Subtotal command. A third is to choose 9 when you wanted hidden rows excluded. A fourth is to copy a subtotal report without selecting only visible cells. A fifth is to add the subtotal rows by hand, so that the formulas are not updated when the data changes. Let Excel insert them and use the Remove All button to start again.
Frequently asked questions
What does SUBTOTAL do in Excel?
It calculates a sum, average, count or other result, and ignores rows hidden by a filter and other SUBTOTAL results.
What is the difference between SUBTOTAL 9 and 109?
Both ignore filtered rows, but 109 also ignores rows hidden by hand.
How do I add subtotals to a list?
Sort the list by the group column, then use Data, Subtotal.
Why is Subtotal greyed out?
The data may be in an Excel table. Convert it to a range first.
How do I remove subtotals?
Use Data, Subtotal, and click Remove All.


