To sum all subtotals in Excel, use the SUBTOTAL function with the 9 or 109 argument for each subtotal row, then sum those results with a simple SUM formula, or use the built-in Subtotal command which automatically inserts a Grand Total row. For a quick manual method, select the column of subtotal values and press Alt+= (Windows) or Command+Shift+T (Mac) to insert a SUM formula at the bottom.
Understanding Subtotals in Excel
Subtotals are intermediate sums that group data by a category, such as summing sales per region before getting a total for all regions. Excel offers two distinct ways to create them: the Subtotal command (Data > Subtotal) and the SUBTOTAL function. The command is automatic and inserts subtotal rows with the SUBTOTAL function inside them, plus a Grand Total row. The function is manual—you write it yourself in any cell. Knowing which one you have affects how you sum them later.
Method 1: Using the Subtotal Command (Easiest)
If you used the Subtotal command, Excel already adds a Grand Total row at the very bottom of your data. To see it, ensure the data is sorted by the grouping column, then go to Data > Subtotal. In the dialog box, choose the column to group by, the operation (Sum), and the column to subtotal. Check the box for Replace current subtotals and Summary below data. Click OK. Excel inserts rows labeled Total for each group and a final Grand Total row. That Grand Total is the sum of all subtotals—no extra work needed.
If you don’t see the Grand Total, it may be hidden. Click the outline numbers (1, 2, 3) on the left to show level 3, which displays all detail rows and the Grand Total. If the Grand Total row is missing, re-run the Subtotal command and ensure Summary below data is checked.
Method 2: Using the SUBTOTAL Function Manually
When you create subtotals manually with the SUBTOTAL function, there is no automatic Grand Total. You must add your own. The function syntax is =SUBTOTAL(function_num, ref1, [ref2], ...). For sums, use 9 (includes hidden rows) or 109 (ignores hidden rows). For example, if your subtotal values are in cells B2, B5, and B8 (each containing a SUBTOTAL formula), you can sum them with:
=SUM(B2, B5, B8)
That works, but if you have many subtotals, a better approach is to sum the entire column and subtract the detail rows, or use a SUMIF to target only subtotal rows. A common trick is to place subtotals in a separate column (e.g., column C) and sum that column directly: =SUM(C2:C100). If your subtotals are in the same column as data, you can use a formula that identifies subtotal rows by a label, like:
=SUMIF(A2:A100, "Total", B2:B100)
This assumes column A contains the word “Total” on subtotal rows, and column B has the values. It sums only those rows, ignoring detail rows.
Method 3: Quick Sum with Alt+= (AutoSum)
If you have a list of subtotal values that are not adjacent (e.g., in cells B2, B5, B8), you can use AutoSum to add them quickly. Click the cell where you want the grand total, press Alt+= on Windows (Command+Shift+T on Mac), and Excel will attempt to guess the range. If it guesses wrong, hold Ctrl (Windows) or Command (Mac) and click each subtotal cell to add them to the formula, then press Enter. This is the fastest manual method for a handful of subtotals.
Common Mistakes When Summing Subtotals
One frequent error is double-counting. If you sum a column that contains both detail rows and subtotal rows, you’ll get a number that is too high because the subtotals already include the detail values. For example, if column B has sales for each product and a subtotal row for each region, summing the whole column gives you the sum of all products plus the sum of all region subtotals—which is double the actual total. Always ensure your sum range includes only subtotal rows, or use the Grand Total row provided by the Subtotal command.
Another issue is using SUM on a range that contains SUBTOTAL functions with 9 or 109. The SUM function will include those subtotals, which is fine if they are the only values, but if you mix them with detail rows, you get the double-count problem. Also, be aware that SUBTOTAL with 109 ignores hidden rows, but SUM does not. If you hide rows, your manual sum may not match the subtotals.
Choosing the Right Approach
The best method depends on your data structure and how often you update it.
- Data > Subtotal command: Best for one-time grouping and when you want Excel to manage the subtotal and grand total rows automatically. It works on a sorted list and creates a collapsible outline.
- Manual SUBTOTAL function: Best when you need subtotals in a specific layout, or when you want to control which rows are subtotaled. It requires you to build the grand total yourself.
- AutoSum with Alt+=: Quick for a small, fixed set of subtotal cells, but not scalable if you add more groups later.
If you’re using Excel Tables (Insert > Table), the Subtotal command is not available. Instead, use the SUBTOTAL function in a separate row, or use PivotTables, which automatically provide grand totals.
Frequently Asked Questions
Why does my sum of subtotals give a different number than the Grand Total?
This usually happens because you accidentally included detail rows in your sum range, or because some subtotal rows are hidden and you used 109 instead of 9 in the SUBTOTAL function. Check your range and ensure it includes only the subtotal cells. Also, if you used the Subtotal command, the Grand Total row itself is a SUBTOTAL function, so summing it with other subtotals would double-count.
Can I sum subtotals that are in different columns?
Yes. Use a formula like =SUM(B2, C5, D8) to add specific cells from different columns. Or, if your subtotals are in a consistent pattern, use SUMIF with a helper column that marks subtotal rows. For example, if column A has “Subtotal” labels, use =SUMIF(A:A, "Subtotal", B:D) to sum all matching rows across columns B, C, and D.
Is there a way to sum subtotals in Google Sheets?
Yes, the same principles apply. Google Sheets has the SUBTOTAL function with the same syntax, and you can use the Data > Subtotal menu (though it’s called “Subtotal” under Data in the menu). The keyboard shortcut for AutoSum is Ctrl+Shift+T on Windows and Command+Shift+T on Mac. The main difference is that Google Sheets does not have the collapsible outline feature that Excel has, so you may need to manually hide rows.
How do I sum subtotals in a PivotTable?
PivotTables automatically show a Grand Total row or column. To display it, right-click any cell in the PivotTable, select PivotTable Options, and on the Totals & Filters tab, check “Show grand totals for rows” and “Show grand totals for columns.” The Grand Total is the sum of all subtotals in that field.
What if my subtotals are in a filtered list?
If you have filtered data, use SUBTOTAL(109, range) to sum only visible cells. For example, =SUBTOTAL(109, B2:B100) will sum only the visible rows in that range, which effectively sums the subtotals if your subtotal rows are visible. If you use SUM, it will include hidden rows, giving an incorrect total.