How to Sum by Group in Excel (4 Methods)

We will use the following dataset to illustrate summing by group. We have groups of items in the Items column, some of which repeat.

dataset to sum by group

Method 1 – Using IF and SUMIF Functions to Sum by Group

We want to sum the amount for each item group in column D.

Steps:

Enter the following formula in cell D5:

=IF(B5=B4,"",SUMIF(B:B,B5,C:C))

It gives the sum of the amounts for the first item category i.e. Bananas.

entering formula using IF & SUMIF functions

Copy the formula to the cells below. It gives the sum of the amounts for each category as follows.

result for sum by group using IF & SUMIF

Method 2 – Summing by Group Using the Subtotal Tool

Select the entire dataset as follows.

Go to the Outline drop-down from the Data tab as shown below.

Click on the Subtotal icon. This opens the Subtotal dialog box.

Check the Amount field and hit OK.

subtotal dialog box to sum by group

You will see the total for each category item obtained in the earlier method.
To get a more organized result, click on the ‘–’ icons one by one. Clicking on the number ‘2’ icon also gives the same result.

You get the sum of the amounts for each group of items as follows.

result for sum by group using the Subtotal tool

Similar Readings

Method 3 – Using UNIQUE and SUMIF Functions to Sum by Group

We will insert two more cells as the criteria.

Enter the following formula in cell E5:

=UNIQUE(B5:B12,FALSE,FALSE)

This filters the unique items in column E.

entering formula of UNIQUE function to sum by group

Enter the following formula in cell F5.

=SUMIF(B5:B12,E5,C5:C12)

This gives the aggregate of the amounts for the first unique item.

using SUMIF function to sum by group

Copy the formula down to the cells below to get the following result.

result for sum by group using UNIQUE & SUMIF functions

Method 4 – Applying a PivotTable

Select the entire dataset as shown below.
From the Insert tab, click on the PivotTable icon.

dataset selection to create PivotTable

Make sure the Items and Amount boxes are checked and they are on the Rows and Values fields as follows. You can drag them to organize properly.

You get the results as follows.

result for sum by group using PivotTable

Things to Remember

The dataset should be sorted before applying the first two methods. Use the Sort & Filter tool.
You can use the SUMIF function only in the third method if you want the sum of one particular group of values at a time.
Calculations were done on Office365. Not all functions might be available in earlier versions.
A PivotTable automatically detects groups of data and creates subtotals.

Download the Practice Workbook

Calculate Sum by Group.xlsx