See all How-To Articles

How to Combine / Merge Duplicate Rows and Sum Them in Excel

In this article, you will learn how to combine duplicate rows by summing them in Excel.

 

combine merge duplicate rows 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 merge duplicate rows initial data

 

You can combine the data and get a total sales amount for every product in a separate range, next to the original one.

1. Select a cell where you want to get a new data range (e.g., E1), and in the Ribbon, go to Data > Consolidate.

 

combine merge duplicate rows consolidate

 

2. In the Consolidate window, leave the default Function (Sum), and click on the Reference icon to select a range for consolidation.

 

combine merge duplicate rows consolidate 1

 

3. Select the data range you want to consolidate (e.g., B1:C17), and click Enter.

 

combine merge duplicate rows consolidate 2

 

4. 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.

 

combine merge duplicate rows consolidate 3

 

The result is total sales summarized by product, starting from cell E1.

 

combine merge duplicate rows consolidate 4