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:

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:

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.

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

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

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.

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.

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.

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

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.

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.

How to Use VLOOKUP Function with Exact Match in Excel

This guide will explain how to use the VLOOKUP function for exact matches in Excel. VLOOKUP is a…

How to Lookup a Value in a 3D Table in Excel

This guide will explain how you can use Excel functions to lookup values in a 3D table in…

How to Apply Chebyshev’s Theorem in Excel

This guide will explain how to apply Chebyshev’s Theorem in Excel. This theorem is often used to find…

How to Make an ANOVA Table in Excel

This guide will explain how to make an ANOVA table in Excel. ANOVA tables allow users to test…