How to Combine Duplicate Rows and Sum the Values in Excel

As a part of my full-time job a few years ago, one of the things I had to deal with was to combine data from different workbooks shared by other people.

And one of the common tasks was to combine the data in such a way that there were no duplicate records.

For example, below is a dataset that has multiple records for the same country.

And the final result needs to be a consolidated dataset where each country is reported only once, with its sales added up.

In this tutorial, I will show you how to combine duplicate rows and sum the values to create a single consolidated dataset.

I’ll cover the Consolidate option, a Pivot Table, and a formula method using the GROUPBY function (plus a fallback formula if your Excel version doesn’t have GROUPBY).

Combine Duplicates and Sum Using the Consolidate Option

If all you need to do is consolidate data and add all the values for the repeating records, it’s best to use the consolidate feature in Excel.

The other methods are to use a Pivot table and summarize the data, or to use a formula (both covered later in this tutorial).

Note that Consolidate may not be available in Excel for the web. If you don’t see it in the Data tab, use one of the other methods below.

Suppose you have a data set as shown below where the country name repeats multiple times, and you want the consolidated data in columns D and E.

While these are unique records as the sales value is different, for reporting purposes you may want to remove multiple instances of the same country and show the sales value as one consolidated sum.

Below are the steps to do this:

1. Copy the headers of the original data and paste it where you want the consolidated data (cells D1 and E1 in this example)

2. Select the cell below the leftmost header (cell D2 in this example)

3. Click the Data tab

4. In the Data Tools group, click on the Consolidate icon

5. In the Consolidate dialog box, select Sum from the function drop-down (if not already selected by default)

6. Click on the range selection icon in the Reference field

7. Select the range A2:B9 (the data excluding the headers), and click the range selection icon again to go back to the dialog box

8. Select the Left column checkbox

The Left column option tells Consolidate to use the country names in the first column as labels, so it adds up the sales for each country.

9. Click OK

The above steps would consolidate the data by removing the duplicate entries and adding the values for each country.

In the end result, you get a unique list of countries along with the sales value from the original data set.

I chose to get the SUM of the values from each record. You can also choose other options such as Count or Average or Max/Min from the Function drop-down.

Important: Consolidate puts plain values in the result cells, so these totals won’t update if the original data changes. You’ll have to run Consolidate again (or use the formula method covered later).

The Consolidate dialog box also has a Create links to source data option, which links the result to the original data so it can update. But Excel only allows it when the source data is on a different worksheet, so it can’t be used in this example.

In this example, I have shown you how to consolidate the data in a single data set in a worksheet. You can also use this feature to consolidate data from multiple worksheets in the same workbook, and even multiple different workbooks.

Also read: Remove Duplicates Within a Cell in Excel

Merge Duplicates and Sum Using a Pivot Table

A Pivot Table is the Swiss army knife of slicing and dicing data in Excel.

It can easily give you a summary which is a combined data set with no duplicates and the values that are the sum of all the similar records, and do a lot more.

The downside of this method as compared to the previous one is that this one takes more clicks and a few more seconds compared to the previous one.

The upside is that when your data changes, you can refresh the Pivot Table and the totals update, which you can’t do with the Consolidate result.

Suppose you have a data set as shown below where the country name repeats multiple times and you want to consolidate this data.

Below are the steps to create a Pivot table:

1. Select any cell in the dataset

2. Click the Insert tab

3. In the Tables group, click on the PivotTable option

4. In the PivotTable from table or range dialog box (called Create PivotTable in older versions), make sure the Table/Range is correct

5. Click on the Existing Worksheet option

6. Select the location where you want the resulting Pivot Table to be inserted (cell D1 in this example)

7. Click OK

The above steps would insert a Pivot table in the selected destination cell.

Now, we can do all sorts of things with a pivot table, including consolidating our data set and removing the duplicates.

Below are the steps to do this:

1. Click anywhere in the pivot table area and it would open the PivotTable Fields pane on the right

2. Drag and put the Country field into the Rows area

3. Drag and put the Sales field into the Values area

The above steps summarize the data and give you the sum of sales for all the countries.

If this is all that you want and you have no need for a pivot table, you can copy the data and paste it as values somewhere else and delete the pivot table.

This will also help you reduce the size of your Excel workbook.

Sum Duplicates Using a Formula (GROUPBY)

If you want a result that updates on its own whenever the data changes, you can use a formula instead.

In Microsoft 365, the GROUPBY function can combine the duplicate rows and sum the values with just one formula.

Below is the same dataset where the country name repeats multiple times, and I want the combined list with the total sales in columns D and E.

Here is the formula that will do this:

=GROUPBY(A2:A9,B2:B9,SUM)

This formula gives you each country only once, along with the sum of its sales. It also sorts the countries alphabetically and adds a Total row at the bottom.

Here, A2:A9 has the countries (the values that repeat), B2:B9 has the sales values to add, and SUM tells GROUPBY how to combine the sales for each country.

Since it’s a formula, the result spills into the cells below on its own, and it updates automatically when you change any of the sales values.

And just like with Consolidate, you can use AVERAGE, COUNT, MAX, or MIN instead of SUM.

The GROUPBY function is only available in Excel for Microsoft 365. In Excel 2024, Excel 2021, or older versions, it returns a #NAME? error.

If you’re using Excel 2021 or Excel 2024, you can get the same totals for each country with the UNIQUE and SUMIF functions.

First, enter this formula in cell D2 to get the list of countries, with each country appearing only once:

=UNIQUE(A2:A9)

Then, enter this formula in cell E2 to get the total sales for each of those countries:

=SUMIF(A2:A9,D2#,B2:B9)

D2# refers to the entire list of countries that the UNIQUE formula returned, so SUMIF gives you one total for each country.

Unlike GROUPBY, this keeps the countries in the order they first appear in the data, and it doesn’t add a total row.

If you are using Excel 2019 or earlier, type the country names in column D yourself, then enter the below formula in cell E2 and copy it down for the other countries:

=SUMIF($A$2:$A$9,D2,$B$2:$B$9)

So these are three quick and easy methods that you can use to consolidate the data where it would combine the duplicate rows and sum all the values in those records.

I hope you found this tutorial useful!

You may also like the following Excel tutorials:

• Combine Data From Multiple Worksheets into a Single Worksheet in Excel
• Combine Data from Multiple Workbooks in Excel
• How to Combine Cells in Excel
• How to Combine Multiple Excel Files Into One Excel Workbook
• Find and Remove Duplicates in Excel