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

Method 1 – Using Remove Duplicates Tool and SUMIF Function

Steps:

Copy the Customer column (make sure you start copying from the header Customer) using CTRL+C or from the ribbon.

Copy Customer Column

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

Paste Customer Column to Combine Duplicate Rows and Sum the Values in Excel

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

Using Remove Duplicates Tool to Combine Duplicate Rows and Sum the Values in Excel

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.

Remove Duplicates Dialogue Box

The duplicates have been removed.

Combined Duplicate Rows

Make a new header beside Customer naming it Total Due for sum.

Total Due Column

Select cell G5 underneath the new header and write the following function using SUMIF function.

=SUMIF($C$5:$C$14,F5,$D$5:$D$14)

Using SUMIF Function to Combine Duplicate Rows and Sum the Values in Excel

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.

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

Drag the Fill Handle Below

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.

Combined Duplicate Rows and Summed the Values in Excel

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

Steps:

Copy and paste the headers from the preliminary data in the desired location.

Copied Columns

Select the cell below the first copied header.
Go to the Data Tab >> Data Tools group >> Consolidate tool.

Using Consolidate Tool to Combine Duplicate Rows and Sum the Values in Excel

The Consolidate dialogue box will appear. In the Function: dropdown box select Sum (it should already be there). Mark the Left Column tick box.
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). Click OK.

Consolidate Dialogue Box to Combine Duplicate Rows and Sum the Values in Excel

Get your sum values in Excel successfully with combined duplicate rows.

Combined Duplicate Rows and Summed the Values in Excel

Note:

Use this tool to combine data from multiple sheets, and even from any number of different workbooks.

Method 3 – Employing Pivot Table Feature

Steps:

Select an empty cell where we will make a Pivot Table.
Go to the Insert tab >> Tables group >> Pivot Table tool.

Insert a Pivot Table to Combine Duplicate Rows and Sum the Values in Excel

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. It is ‘Employing Pivot Table’!$F$4 for inputting the value at cell F4 in the Employing Pivot Table worksheet. Click on OK.

PivotTable from Table or Range Window

A pivot table is created.
Go to the PivotTable Fields pane on the right.
Drag to put the Customer field into the Rows area and Sum of Due into the Values area.

PivotTable Fields Pane

Get the Sum of dues of all customers with their names in a Pivot Table.

Combined Duplicate Rows and Summed the Values in Excel

Method 4 – Applying VBA Code to Combine Duplicate Rows and Sum Values

Steps:

Go to the Developer tab >> Visual Basic tool.

The VB Editor window will open.
Go to the Insert tab >> Module option.

A new module named Module1 will be created.
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

Press Ctrl + S.

VBA Code to Combine Duplicate Rows and Sum the Values in Excel

A Microsoft Excel dialogue box will appear.
Click on the No button.

Microsoft Excel Dialogue Box

The Save As dialogue box will appear.
Choose the Save as type: option as .xlsm file and click on the Save button.

Save As Window

Close the VB Editor and go to the Developer tab >> Macros tool.

The Macro window will appear.
Choose the Sum_Duplicate_Row_Values macro and click on the Run button.

Run Macro to Combine Duplicate Rows and Sum the Values in Excel

The created Combine Duplicate Rows & Sum Values dialogue box will appear.
Refer to the cells C4:D14 in the Range text box and click the OK button.

Combine Duplicate Rows and Sum the Values Dialogue Box