Excel Group by Sum
While working with excel, we get a lot of new scenarios which have not imagined at all, 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 recently 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 many 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 a 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 the SUM function to arrive at each city total. But the problem with the above method is we have used the 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.
- 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 the 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 a blank as a 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 the 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, giving 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 the 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 an 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, the logical cell 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 the 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 a downloadable excel template. You may learn more about excel from the following articles –