How to Calculate Sum by Group in Excel

This guide will explain how to calculate the sum by group in Excel.

Since Excel has several built-in functions and tools, it is an excellent tool to use for mathematical and statistical calculations. With the help of functions, we can easily perform different calculations. For instance, we can easily calculate the sum by group in Excel.

Basically, sum by group means we will add up all the values of a specific category or column. In this case, we can use several functions to perform this task. So we will use the IF function, SUMIF function, and UNIQUE function to calculate the sum by group.

Let’s take a sample scenario wherein we must calculate the sum by group in Excel.

Suppose you have a data set containing different categories of products and their prices. And we want to sum the total prices of each category. To make this task easier, you opted to use the IF function and SUMIF function. 

Before we move on to a real example of calculating sum by group in Excel, let’s first explore how to write the functions we will be using in the formulas.

 

The Anatomy of the UNIQUE Function

The syntax or the way we write the UNIQUE function is as follows:

=UNIQUE(array, [by_col], [exactly_once])

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we start any function in Excel.
  • UNIQUE() is our UNIQUE function. And this function is used to return all unique values from the selected range or array.
  • array is a required argument. And this refers to the range or array from which we want to return the unique rows or columns.
  • by_col is an optional argument. So this is a logical value. If FALSE, the function will compare rows against each other and return the unique rows. If TRUE, the function will compare columns against each other and return the unique columns. 
  • exactly_once is another optional argument. And this is also a logical value. If TRUE, the function will return rows or columns that occur exactly once from the array. If FALSE or omitted, the function will return all distinct rows or columns from the selected array. 

The Anatomy of the SUMIF Function

The syntax or the way we write the FILTER function is as follows:

=SUMIF(range, criteria, [sum_range])

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we begin any function in Excel.
  • SUMIF() refers to our SUMIF function. And this function is used to add the cells specified by a given condition or criteria.
  • range is a required argument. So it refers to the range of cells we want to be evaluated. 
  • criteria is another required argument. And this refers to the condition or criteria, which can be in the form of a number, expression, or text that defines which cells will be added.
  • sum_range is an optional argument. And it refers to the actual cells to sum. When omitted or left blank, the cells in the range are used.

Great! Now let’s dive into a real example of calculating sum by group in Excel.

 

A Real Example of Calculating Sum by Group in Excel

Let’s say we have data set containing a list of items of different categories and their prices. And we want to calculate the sum of each category. So our initial data set would look like this:

Initial dataset

 

For instance, we have a list of fruit products and their corresponding prices. So we want to get the sum of each fruit category. Let’s say we want to get the total sum of prices for the bananas. So we can perform two easy and simple methods.

Firstly, we can sum by group in Excel using the IF and SUMIF functions. So this formula will give the sum of the amounts for the first category we input. In this case, the formula immediately gives the sum of the bananas group.

Secondly, we can also use the UNIQUE function together with the SUMIF function to sum by group in Excel. Furthermore, the second method filters the unique items first. Then, it will give the total sum of the unique items. 

So the UNIQUE function will return a list of the unique items in the data set. After, we will use the SUMIF function to get the sum of the unique items.

And our final data set would look like this:

Final dataset

 

You can make your own copy of the spreadsheet above using the link attached below. 

Amazing! Now let’s dive in and learn the steps of how to calculate sum by group in Excel.

 

How to Calculate Sum by Group in Excel

In this section, we will explain the step-by-step process of how to calculate sum by group in Excel. Furthermore, each step contains detailed instructions and pictures to guide you along the way.

1. Firstly, we will use the formula combining the IF and SUMIF functions. So we need to create a new column to input the results. Then, we will type in the formula “=IF(B3=B2,””,SUMIF(B:B, B3, C:C))”. Lastly, we will press the Enter key to return the results.

Sum by Group in Excel

 

2. Secondly, drag down the Fill Handle tool to copy the formula and apply it to the other cells.

Drag down formula

 

3. And tada! We have successfully calculated the sum by group in Excel using the IF function and SUMIF function.

Final output

 

4. Next, we will try another method to calculate the sum by group. In this case, we will use the UNIQUE function together with the SUMIF function. So we will create a new table to input the results. In the first column, we will place the unique items.

Then, we will type in the formula “=UNIQUE(B11:B16, FALSE, FALSE)”. Lastly, we will press the Enter key to return the results.

Sum by Group in Excel

 

5. In the second column of the table, we will place the sum by group results. So we will input the formula “=SUMIF(B11:B16,E11,C11:C16)”. Finally, we will press the Enter key to return the results.

Sum by Group in Excel

 

6. Afterward, we will drag down the Fill Handle tool to copy and apply the formula to the rest of the cells in the column.

Sum by Group in Excel

 

7. And tada! We have successfully calculated the sum by group using the UNIQUE function.

Sum by Group in Excel

 

And that’s pretty much it! We have explained how to calculate sum by group in Excel. Furthermore, we discussed two easy and simple methods to perform this task. Now you can choose any of the two methods and use them in your own work.

Are you interested in learning more about what Excel can do? You can now use the UNIQUE function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like