Table of Contents
The process of combining duplicate rows in Excel and summing the corresponding values involves identifying and merging rows with identical data in a specific column, and then calculating the total of the corresponding values in another column. This technique is useful for condensing and organizing large data sets, allowing for easier analysis and interpretation. By using the “Consolidate” function or other built-in Excel features, users can efficiently combine duplicate rows and accurately sum their associated values, saving time and increasing efficiency in data management tasks.
Excel: Combine Duplicate Rows and Sum
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:
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: