How to Sum by Category in Excel

While working with Excel, we can sum values ​​that meet more than one criterion using simple functions. This step-by-step tutorial will help users of all skill levels to sum by category in Excel , that is, to sum values ​​by group using the IF and SUMIF functions.

Pay attention to the procedure and the illustrations provided and you will see that the process is quite simple and can be used for any field where you need to sum a list of values ​​of related items.

IF Function Syntax

The IF function evaluates a given logical test and returns TRUE or FALSE.

=IF(logical_test, [value_if_true], [value_if_false])

The value_if_true and value_if_false arguments are optional. If left blank, the function will return TRUE if the logical test is met and FALSE otherwise.

You might also be interested in: How to Add and Subtract Percentages in Excel

SUMIF Function Syntax

SUMIF sums the values ​​in a specified range, based on a given criterion

=SUMIF(range, criteria, [sum_range])

The parameters are:

• Rank: the range of data to be evaluated using the criteria.
• Criterion: the criteria or condition that determines which cells will be added.
• Sum_range: the cells to be added; if left blank, “sum_range” = “range” means the data range to be added is the same as the data range evaluated.

Setting up data to sum by category in Excel

Our table has three columns: Product (column B), Orders (column C), and Sum by Group (column D). We want to group the products and calculate the sum by group. The results will be recorded in column D.

We have three products namely: laptop, speaker and tablet. First, we need to sort our data by product through these steps:

• Step 1: select the range for our data, B2:D12
• Step 2: click on the tab Facts & figures, then select Create a filter.

• Step 3: Click the Product drop-down arrow.
• Step 4: Choose Sort AZ.

The resulting table will show the products correctly sorted.

Sum of orders by product

Now that we have sorted our data by product, we can sum the orders by product using the IF and SUMIF functions. Our goal is to display the sum in the first row that a product appears. Let's follow these steps:

• Step 1: select cell D3.
• Step 2: enter the formula: =IF(B3=B2,»»,SUMIF($B$3:$B$12,B3,$C$3:$C$12))
• Step 3: press Enter.
• Step 4: Copy the formula from cell D3 to cells D4:D12 by clicking the “+» in the lower right corner of cell D3 and dragging it down.

The formula tests whether the value in B3 is equal to the value in the previous cell B2. If the logical test returns TRUE, the IF function will return an empty string. Otherwise, it will sum the orders in column C corresponding to the product in column B. Since B3 “Laptop” is not equal to B2 “Product,” our formula returns the sum of orders for “Laptop.”

As a result, the total orders for Laptop in cell D3 is 4500, which is the sum of 1000, 500, 1000, and 2000 in cells C3:C6. For cells D4:D6, the formula returns an empty string, because the value in B4:B6 is equal to the previous cell, which is Laptop.

You might also be interested in: Data Bars in Excel. What They Are and How to Add Them

For the following cells in column D, the formula returns the sum of orders for “Speaker” and “Tablet” in cell D7 and D10, respectively. The cells in between are blank or empty because our IF formula evaluates them to TRUE. The following table shows the final result of the sum of orders by product.

Another example

To illustrate once again how to sum by category in Excel, let's suppose we have the following set of data showing the total points scored by 15 different basketball players:

Now suppose we would like to find the sum of the points scored, grouped by team. To do so, we can use the =UNIQUE() function to first create a list of the unique teams. We will write the following formula in cell F2:

= UNIQUE ( B2: B16 )

Once we press Enter, a list of unique team names will be displayed:

Next, we can use the =SUMIF() function to find the total sum of points scored by the players on each team. We'll write the following formula in cell G2:

=SUMIF(B2:B16,F2,C2:C16)

We will then copy and paste this formula into the remaining cells in column G:

That's it! Column F shows each of the unique teams and column G shows the sum of points scored by each team.

You need to learn: How to Use the Left and Right Functions in Excel

Final thoughts

Okay, now you know how to sum by category in Excel, or in other words, how to sum values ​​by group in an Excel spreadsheet. This is quite useful in many fields, especially in retail, where you work with countless products that frequently enter and leave inventory. Calculating by summing value by value would be a tedious task, but we don't need to go through that thanks to the tools Microsoft Excel provides.