How to sum subtotals in Excel
You can watch a video tutorial here.
In Excel, it is possible to group rows to arrange the data into categories. Once rows are grouped, sub-totals can be added to calculate the sum of a particular column within each category. When that has been done, you may want to sum up all the sub-totals to get the total value for the data. If you use the SUM() function on the entire column, the individual values and the sub-totals will be added together.
Here we will see 2 ways to sum only the sub-totals, one using the SUM() function and the second using the SUBTOTAL() function.
- SUM() function: this returns the sum of a range of numbers or individual numbers
- Syntax: SUM(numbers)
- numbers: this is a range of numbers or individual ranges/numbers separated by a comma
- Syntax: SUM(numbers)
- SUBTOTAL() function: this returns the subtotal for a column using a specified aggregation function
- Syntax: SUBTOTAL(function_code, ranges)
- function_code: this is a number that corresponds to an aggregation function e.g. 1 is AVERAGE, 2 is COUNT, 9 is SUM etc.
- ranges: the range of the cells on which the function is to be applied
- Syntax: SUBTOTAL(function_code, ranges)
Option 1 – Sum the sub-totals individually
Step 1 – Create the formula
- Select the cell where the result is to be displayed
- Type the formula using cell references:
=SUM(Sub-total1, Sub-total2, Subtotal3)
- Press Enter
Step 2 – Copy the formula
- Using the fill handle from the first cell, drag the formula to the remaining cells
OR
- Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
- Select the rest of the cells in the row and press Ctrl+V or choose Paste from the context menu (right-click)
Option 2 – Use the SUBTOTAL() function
Step 1 – Create the formula for each sub-total
- Select the cell where the first sub-total is to be displayed
- Type the formula using cell references:
=SUBTOTAL(9,range of the first group)
- Press Enter
- Repeat the steps above for each sub-total
Step 2 – Copy the formula
- Using the fill handle from the first sub-total cell, drag the formula to the remaining cells
OR
- Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
- Select the rest of the cells in the row and press Ctrl+V or choose Paste from the context menu (right-click)
- Repeat the above step for each row of sub-totals
Step 3 – Create the total
- Select the cell where the result is to be displayed
- Type the formula using cell references:
=SUM(range of the column)
- Press Enter
Step 4 – Copy the formula
- Using the fill handle from the first cell, drag the formula to the remaining cells
OR
- Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
- Select the rest of the cells in the row and press Ctrl+V or choose Paste from the context menu (right-click)