Md. Shamim Reza
To sum by group means to add up values of a particular category. We often use it on numerous occasions in our daily lives. The summation of a group of values can be calculated in Excel in different ways. This article shows the 4 easiest ways to sum by group in Excel.
Download Practice Workbook
You can download the practice workbook from here.
4 Easiest Ways to Sum by Group in Excel
We will show you the 4 easiest ways to sum by group in this article. We will use the following dataset to illustrate these methods.
1. Sum by Group Using IF and SUMIF Functions
Suppose, you want to sum the amount for each item group in column D.
Then follow the steps below.
Step 1: First 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.
Step 2: After that 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)
2. Sum by Group Using Excel Subtotal Tool
You can also sum the amounts for each item category by using the Subtotal tool of Excel. To do that, apply the following steps.
Step 1: First, select the entire dataset as follows.
Step 2: Then go to the Outline drop-down from the Data tab as shown below.
Step 3: Now click on the Subtotal icon. This opens the Subtotal dialog box.
Step 4: After that, check the ‘Amount’ field and hit OK.
Step 5: Below, you will see the total for each category item obtained in the earlier method. Now, to get a more organized result, click on the ‘–’ icons one by one. Clicking on the number ‘2’ icon also gives the same result.
And now, 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)
3. Sum by Group Using UNIQUE and SUMIF Functions
Another alternative way to sum values by group is to use the UNIQUE and SUMIF functions. This method filters the unique items first. Then it gives the summation of the amounts for that unique item. To apply this method, follow the steps below.
Step 1: Enter the following formula in cell E5:
=UNIQUE(B5:B12,FALSE,FALSE)This filters the unique items in column E.
Step 2: Now 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.
Step 3: Lastly, 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)
4. Summation by Group Using Excel PivotTable
Another easy but amazing way to get the summation of those amounts by item category is to use the PivotTable tool. For that, follow the steps below.
Step 1: At first, select the entire dataset as shown below. Then from the Insert tab, click on the PivotTable icon.
Step 2: Now make sure the ‘Items’ and ‘Amount’ tables are checked and they are on the ‘Rows’ and ‘Values’ fields as follows. You can drag them to organize properly.
Finally, 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. Using the Sort & Filter tool of Excel might be helpful to do so.
- 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. Look for the steps in your Microsoft Office version if you use a different one.
- PivotTable automatically detects the same group of data. So no need to worry about sorting your data while following the last method.
Conclusion
Now, you know the easiest ways to sum values by category. We have used IF, SUMIF, and UNIQUE functions, the Subtotal and PivotTable tools of Excel in those methods. If you have further queries or suggestions, please share them with us in the comment section. We hope this article has helped you to understand how to sum values by group.
