Excel Group by Sum
While working with excel we get a lot of new scenarios which have not imagined at all, so as to excel daily user we should be in a position to encounter those scenarios. We cannot pinpoint at any one situation but for sure those unique situations will arise. One of the particular situations we have faced in recent times was getting the sum of multiple line items into one cell when we have all the line item values in one column. So in this article, we will show you how to group by sum values in excel.
How to Sum Values by Group in Excel?
Below is how to use sum values by the group in excel with examples.
Examples #1 – Sum Group-Wise in Excel
When you receive the data there are a lot of line items and when you summarize it we may get a summary total in very lines, so this is the common task we all have done by using a pivot table tool. However, summing the values of all the items of a single group in a single cell is a different task without using a pivot table.
- For example, look at the below data in excel.
For the above data, we need to create a city-wise sum of values in single-cell by arranging all the cities in order.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
- For your reference, we need to get the sum of each city like the below one.
In the above image, we have an extra column as “City Total” and all the cities are sorted one after the other, then we have used SUM function to arrive each city total. But the problem with the above method is we have used SUM excel function and for each city, there are different line items and for each city, we need to apply the SUM function individually, so it takes a lot of time when the data is large.
So to the city-wise total, we can use the combination formula of IF & SUMIF functions.
Example#2 – Combination Formula to Get Group-Wise Sum in Excel
Take the above data for this instance as well.
- Sort the data based on city names.
- Now we need the city total in the row where the city name starts and in the remaining cells we just need blank values. So open IF function now.
- The logical test that we need to apply here is to test whether the below cell has the same city name as the active cell, so apply the logical test as below.
- If the above cell value is equal to the active cell value then we need blank as the result.
- The above logical test says if A2 is equal to A1 returns the result as blank, next if this logical test is FALSE then we need the result as the overall total of that particular city, for this open SUMIF function inside the IF excel function.
- The first argument of SUMIF function is Range i.e. based on what range you would like to SUM, so here based on city name we need to sum so choose the entire column of the city name.
- The next argument is Criteria i.e. in the selected Range for which city you need to sum, so give the cell reference as A2.
- Sum Range is nothing but for the provided criteria which column range you want to sum, so choose the “Sales” column.
- Close two brackets and hit enter key to get the result.
- Now apply the formula to all the cells to get the sum of sales values based on city names.
Now let me explain the result.
- In cell C2 we have the city “Bangalore” total because the logical test of IF function says if cell A2 = A1 then returns empty cell or else gives the overall total of “Bangalore” city. Since the A2 = A1 logic is not correct it has returned the sum of the city “Bangalore”.
- Now come to the second result in the cell D3 we have got the empty result because in this cell A3 = A2 logical tests is TRUE so the result is empty.
- But when the formula reaches D5 cell logical test is A5 = A4, so in this case, in cell A5 we have “Hyderabad” and in cell A4 we have “Bangalore” so both are different and this time SUMIF returns the total of “Hyderabad”.
- This combination of IF & SUMIF can be used to return a sum based on a group of values.
Things to Remember
- You need to sort the data based on the group you are targeting. In this example our target was to get the total based on the city, so we have sorted based on the city.
- With Pivot Table we get the overall summary.
This has been a guide to Excel Group Sum. Here we learn how to sum values by excel group and using IF and SUMIF function along with examples and downloadable excel template. You may learn more about excel from the following articles –