Table of Contents
Often you may want to sum the values in one column in Excel based on the value in another column.
For example, you may want to sum the values in the Points column of the following dataset based on the corresponding value in the Team column:
Fortunately this is easy to do using the UNIQUE and SUMIF functions in Excel.
The following example shows how to do so.
Example: How to Sum Based on Values in Another Column in Excel
Suppose we have the following dataset in Excel that contains information about points scored by basketball players on various teams:
Suppose we would like to sum the values in the Points column based on the values in the Team column.
To do so, we can first create a list of unique values in the Team column by typing the following formula into cell D2:
=UNIQUE(A2:A13)
The following screenshot shows how to use this formula in practice:
We now have a list of unique team names from the original dataset.
Next, type the following formula into cell E2 to calculate the sum of points values for each unique team:
=SUMIF($A$2:$A$13, D2, $B$2:$B$13)
We can then click and drag this formula down to each remaining cell in column E:
Column E now shows the sum of values in the Points column for each unique team name in the Team column.
For example, we can see that the sum of points values scored for all players on the Mavs team is 92.
We can confirm this is correct by manually calculating the sum of points for each player on the Mavs:
Sum of Points for Mavs: 28 + 25 + 19 + 20 = 92
This matches the value calculated by our formula.