Table of Contents
In Excel, you can use the SUMIF() function to sum all values in a range that meet a certain criteria. This is especially useful when you need to total values by category. The SUMIF() function requires three arguments: the range to be evaluated, the criteria to determine which values to sum, and the range of cells containing the sum. To sum values by category, you must use a range of cells containing the criteria, such as a column header. When using the SUMIF() function, make sure that the criteria in the first argument is in the same format and data type as the criteria in the second argument.
Often you may want to sum the values of some dataset in Excel based on a category or group.
For example, suppose we have the following dataset and we’d like to sum the total “points” by team:
The following step-by-step example shows how to do so.
Step 1: Enter the Data
First, enter the data values into Excel:
Step 2: Find the Unique Categories
Next, we need to use the =UNIQUE() function to produce a list of unique values in a certain range.
In our example, we’ll type the following formula in cell E2:
=UNIQUE(B2:B12)
This will produce a list of unique teams:
Step 3: Find the Sum by Category
Next, we will use the SUMIF(range, criterion, sum_range) function to find the sum of the points scored by each team.
In our example, we’ll type the following formula in cell F2:
=SUMIF($B$2:$C$12, E2, $C$2:$C$12)
This tells us:
- The total points scored by players on the Lakers is 21.6.
- The total points scored by players on the Mavericks is 56.4.
- The total points scored by players on the Spurs is 48.8.
- The total points scored by players on the Warriors is 65.