Often you may want to combine rows with duplicate values in Excel and sum the values in another column:
Fortunately this is easy to do using the UNIQUE and SUMIF functions in Excel.
The following example shows how to do so in practice.
Example: How to Combine Duplicate Rows and Sum in Excel
Suppose we have the following dataset in Excel that contains information about points scored by basketball players on various teams:
Notice that several of the rows have the same value in the Team column.
Suppose we would like to combine the rows with the same Team values and simply sum the values in the Points 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:
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)
The following screenshot shows how to use this formula in practice:
We have now successfully combined all duplicate values in the Team column and calculated the sum of values in the Points column for each unique team.
For example:
- The sum of points values scored for all players on the Mavs team is 92.
- The sum of points values scored for all players on the Spurs team is 127.
And so on.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Highlight Duplicates in Two Columns
Excel: How to Remove Both Duplicate and Original Values
Excel: How to Remove Duplicates but Keep Row with Max Value
