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.

**Read More:** **Sum to End of a Column in Excel (8 Handy Methods)**

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

**Read More:** **Shortcut for Sum in Excel (2 Quick Tricks)**

**Similar Readings**

**How to Sum Selected Cells in Excel (4 Easy Methods)****Sum Only Positive Numbers in Excel (4 Simple Ways)****How to Sum Only Visible Cells in Excel (4 Quick Ways)****[Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)**

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

**Read More:** **How to Sum Filtered Cells in Excel (5 Suitable Ways)**

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

**Read More:** **How to Sum Range of Cells in Row Using Excel VBA (6 Easy Methods)**

**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**

## Further Readings

**How to Sum Colored Cells in Excel (4 Ways)****Sum Cells in Excel: Continuous, Random, With Criteria, etc.****How to Sum If Cell Contains Specific Text in Excel (6 Ways)****Sum Multiple Rows and Columns in Excel****How to Add Multiple Cells in Excel (6 Methods)**