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.

1. SUM() function: this returns the sum of a range of numbers or individual numbers
   1. Syntax: SUM(numbers)
      1. numbers: this is a range of numbers or individual ranges/numbers separated by a comma
2. SUBTOTAL() function: this returns the subtotal for a column using a specified aggregation function
   1. Syntax: SUBTOTAL(function_code, ranges)
      1. function_code: this is a number that corresponds to an aggregation function e.g. 1 is AVERAGE, 2 is COUNT, 9 is SUM etc.
      2. ranges: the range of the cells on which the function is to be applied

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

1. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
2. 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

1. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
2. 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

1. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
2. Select the rest of the cells in the row and press Ctrl+V or choose Paste from the context menu (right-click)