# 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)