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)
