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 F3:
=SUMIF($B$2:$C$12, E3, $C$2:$C$12)
This will calculate the sum of points scored by each team:
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.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Sum Across Multiple Sheets in Excel
How to Sum by Month in Excel
How to Count by Group in Excel