Excel Group Sum

Excel Group by Sum

While working with Excel, we get many new scenarios that have not been imagined. To Excel daily users, we should be able to encounter those scenarios. Of course, we cannot pinpoint any situation, but those unique situations will surely 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 sum values in Excel.

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Excel Group Sum (wallstreetmojo.com)

How to Sum Values by Group in Excel?

Below is how to use the sum values by the group in Excel with examples.

You can download this Group Sum Excel Template here – Group Sum Excel Template

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 the same lines, so this is the common task we all have done using a pivot tableUsing A Pivot TableA Pivot Table is an Excel tool that allows you to extract data in a preferred format (dashboard/reports) from large data sets contained within a worksheet. It can summarize, sort, group, and reorganize data, as well as execute other complex calculations on it.read more 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.

We need to create a city-wise sum of values in a single cell for the above data by arranging all the cities in order.

• We need to create a city-wise sum of values in a single cell for the above data by arranging all the cities in order.

In the above image, we have an extra column as “City Total,” and all the cities are sorted one after the other. Then we used the SUM function to arrive at each city’s total. But the problem with the above method is we have used the SUM excel functionSUM Excel FunctionThe SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges.read more, 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 for the city-wise total, we can use the combination formula of IF and SUMIF functionsSUMIF FunctionsThe SUMIF Excel function calculates the sum of a range of cells based on given criteria. The criteria can include dates, numbers, and text. For example, the formula “=SUMIF(B1:B5, “<=12”)” adds the values in the cell range B1:B5, which are less than or equal to 12.
read more.

Example#2 – Combination Formula to Get Group-Wise Sum in Excel

Take the above data for this instance as well.

1. Sort the data based on city names.

   

2. We need the city total in the row where the city name starts. In the remaining cells, we need blank values. So, we must open the IF function now.

   

3. We need to apply the logical test here to test whether the below cell has the same city name as the active cell, so use the logical test as below.

   

4. If the above cell value equals the active cell value, we need a blank.

   

5. The above logical test says if A2 is equal to A1, it returns the result as blank. Next, if this logical test is “FALSE,” we need the result as the overall total of that particular city for this open SUMIF function inside the IF Excel function.

   

6. The first argument of the SUMIF function is “Range,” i.e., based on what range we would like to “SUM.” So here, based on the city name, we need to sum, so we must choose the entire column of the city name.

   

7. The next argument is “Criteria,” i.e., in the selected range for which city you need to sum, giving the cell reference as A2.

   

8. The sum range is nothing, but for the provided criteria, which column range do we want to sum, so choose the “Sales” column.

   

9. We must close two brackets and press the “Enter” key to get the result.

   

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

Things to Remember

• We need to sort the data based on the group we are targeting. In this example, our target was to get the total based on the city, so we sorted based on the city.
• With the PivotTable, we get the overall summary.

Recommended Articles

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 –

• Group Data in Excel
• How to Group Rows in Excel
• Group in Excel
• Group Excel Worksheets