How to Combine Duplicate Rows and Sum the Values in Excel

Microsoft Excel is a productivity software used to process various kinds of data in various fields. It can help you in bookkeeping and analyzing data which if you want to calculate manually will take huge time and effort. While entering data there may be sometimes when you need to input duplicate data (i.e. shopping cost of the same customer). But when aggregating data you will need summary data that will represent the total value of a particular entry (i.e. total shopping cost of a customer). So, here, we will learn how to combine duplicate rows and sum their values in Excel.

Download Practice Workbook

You can download our practice workbook from here for free!

4 Suitable Ways to Combine Duplicate Rows and Sum the Values in Excel

Say, we have a list containing the dues of customers from December 1, 2021, to December 13, 2021. There are rows that contain the same customer on different dates. Now, you want to get an overall view of what amount of dues are for each customer. Follow any of the 4 suitable ways to accomplish your result in this regard.

1. Using Remove Duplicates Tool and SUMIF Function

You can use the Remove Duplicates tool of Excel and use the SUMIF function to duplicate rows and sum values in Excel.

📌 Steps:

• First, copy the Customer column (make sure you start copying from the header Customer) using CTRL+C or from the ribbon.

• Afterward, select any cell where you want to paste it (cell F4 here) >> go to the Home tab >> click on Paste button.

• Now while selecting the copied cells, go to Data Tab >> Data Tools group >> Remove Duplicates tool.

• As a result, the Remove Duplicates dialogue box will appear. Make sure to mark My data has headers tick box. Select the listed columns (in our case, Customer) and then press OK.

• Thus, the duplicates have been removed.

• At this time, make a new header beside Customer naming it Total Due for sum.

• Now, select Cell G5 underneath the new header and write the following function using SUMIF function.

which refers to calculating the summation value of F5 according to the data in D$5:D$14 corresponding to the names in the range of C$5:C$14. You can adjust the formula accordingly.

• Now, copy this formula to the next few cells by dragging the fill handle below up to the cell where the column of Customer ends.

Thus, the formula will be copied to all the cells below and you will be able to sum the values of your duplicate rows in Excel.

2. Using Consolidate Tool to Combine Duplicate Rows and Sum the Values

Another frequent approach to achieve this result is to use the Consolidate tool of Excel. Follow the steps below to do this.

📌 Steps:

• First, copy and paste the headers from the preliminary data in the desired location.

• Afterward, select the cell below the first copied header.
• Following, go to the Data Tab >>  Data Tools group >> Consolidate tool.

• As a result, the Consolidate dialogue box will appear. In the Function: dropdown box select Sum (it should already be there). Don’t forget to mark the Left Column tick box.
• Now the most important part. Click into the Reference box and using a mouse select the cells without headers (it is very important that you do that) or you can manually input cells range (don’t forget to use $ to make cells absolute – i.e. in our example it is $C$5:$D$14. You know what? Use a mouse, that way excel will input it automatically). Then click OK.

Done! You will get your sum values in Excel successfully with combined duplicate rows.

3. Employing Pivot Table Feature

Pivot Table is a do it all kind of feature in excel. we can do all sorts of things with a Pivot Table – including consolidating our data set and removing the duplicates with their sum. It is a powerful tool. Follow the steps below to accomplish your desired result with pivot table.

📌 Steps:

• At the very beginning, select an empty cell where we will make a Pivot Table.
• Following, go to the Insert tab >> Tables group >> Pivot Table tool.

• Consequently, the PivotTable from table or range dialogue box will appear. For the data to analyze in the Table/Range: text box, select the range with a mouse just like Consolidation but with headers. This time in the box a new term for sheet name will also show up as pivot table can be used to get data from different worksheets too. Like in our example it is ‘Employing Pivot Table’!$C$4:$D$14 for selecting cells C4 to D14 in the Employing Pivot Table sheet.
• To input to a cell in the current worksheet select Existing Worksheet and in the location select a cell with the mouse or write ‘Worksheet Name’!Cell Id . Make sure you make the cell absolute. Like in our cell it is ‘Employing Pivot Table’!$F$4 for inputting the value at Cell F4 in the Employing Pivot Table worksheet. Then click on OK.

• Thus, a pivot table is created.
• Now, go to the PivotTable Fields pane on the right.
• Following, drag to put the Customer field into the Rows area and Sum of Due into the Values area.

Thus, we will get the Sum of dues of all customers with their names in a Pivot Table.

4. Applying VBA Code to Combine Duplicate Rows and Sum Values

Moreover, you can apply a VBA code to combine duplicate rows and sum values in Excel. Go through the steps below to accomplish this.

📌 Steps:

• Initially, go to the Developer tab >> Visual Basic tool.

• As a result, the VB Editor window will open.
• Now, go to the Insert tab >> Module option.

• Consequently, a new module named Module1 will be created.
• Now, double-click on Module1 and write the following code in the code window.

Sub Sum_Duplicate_Row_Values()
Dim r As Range
Dim x As Variant
Dim a As Variant
On Error Resume Next
BoxTitle = "Combine Duplicate Rows & Sum Values"
Set r = Application.Selection
Set r = Application.InputBox("Range", BoxTitle, r.Address, Type:=8)
Set x = CreateObject("Scripting.Dictionary")
a = r.Value
For i = 1 To UBound(a, 1)
    x(a(i, 1)) = x(a(i, 1)) + a(i, 2)
Next
Application.ScreenUpdating = False
r.ClearContents
r.Range("A1").Resize(x.Count, 1) = Application.WorksheetFunction.Transpose(x.keys)
r.Range("B1").Resize(x.Count, 1) = Application.WorksheetFunction.Transpose(x.items)
Application.ScreenUpdating = True
End Sub

• Subsequently, press Ctrl + S.

• As a result, a Microsoft Excel dialogue box will appear.
• Following, click on the No button.

• Consequently, the Save As dialogue box will appear.
• Following, choose the Save as type: option as .xlsm file and click on the Save button.

• Afterward, close the VB Editor and go to the Developer tab >> Macros tool.

• Thus, the Macro window will appear.
• Subsequently, choose the Sum_Duplicate_Row_Values macro and click on the Run button.

• As a result, the created Combine Duplicate Rows & Sum Values dialogue box will appear.
• Following, refer to the cells C4:D14 in the Range text box and click on the OK button.

Thus, you will get your desired result in the existing columns.

Conclusion

So, in this article, we have learned 4 suitable ways to combine duplicate rows and sum their values in excel. We hope you will find these methods intuitive and easy to follow. These types of problems are very common in many excel operations so we tried to help you to solve these problems with less effort. If you have any suggestions about how we could improve ourselves, it would be great. Please provide feedback about what you liked in this article or where you think we should improve in the comment section. Make sure to rate this article and visit ExcelDemy to find more articles like this. Thank you!

Further Readings

• How to Combine Rows with Same ID in Excel (3 Quick Methods)
• How to Combine Multiple Rows into One Cell in Excel
• Merge Duplicate Rows in Excel (3 Effective Methods)
• How to Combine Rows into One Cell in Excel (4 Methods)
• How to Merge Rows in Excel Based on Criteria (Easiest Ways)