This tutorial demonstrates how to combine duplicate rows by summing them in Excel.
Combine Duplicate Rows
In Excel, there is often a need to combine duplicate rows in a range and sum them in a separate column. For example, if you have sales by product in a sheet (as shown below), you may want to summarize sales for every product. In the following example, you have products in Column B (including duplicates), and their sales amounts in Column C.
Combine the data and get a total sales amount for every product in a separate range, next to the original one.
- Select the cell where you want to get a new data range (e.g., E1), and in the Ribbon, go to Data > Consolidate.
- In the Consolidate window, leave the default Function (Sum), and click on the Reference icon to select the range for consolidation.
- Select the data range you want to consolidate (e.g., B1:C17), and press ENTER.
- In the Consolidate window, check Top row, and Left column and click OK.
If the data set doesn’t have a header row, you don’t need to select Top row.
The result is total sales summarized by product, starting from cell E1.