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

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

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

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

• 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.

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

• 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.

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

## Method 4 – Applying a PivotTable

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

• 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.

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