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:

Excel combine duplicate rows and sum

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.

x