How to Combine Duplicate Rows and Sum the Values in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


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

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.

Sample Dataset to Combine Duplicate Rows and Sum the Values in Excel


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.

Copy Customer Column

  • Afterward, 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

  • Now 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

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

Remove Duplicates Dialogue Box

  • Thus, the duplicates have been removed.

Combined Duplicate Rows

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

Total Due Column

  • Now, 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

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.

Drag the Fill Handle Below

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.

Combined Duplicate Rows and Summed the Values 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.

Copied Columns

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

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

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

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

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

Combined Duplicate Rows and Summed the Values in Excel

Note:

You can also use this tool to combine data from multiple sheets, and even from any number of different workbooks.


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

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

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

PivotTable from Table or Range Window

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

PivotTable Fields Pane

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

Combined Duplicate Rows and Summed the Values in Excel


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.

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

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

Microsoft Excel Dialogue Box

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

Save As Window

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

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

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

Combine Duplicate Rows and Sum the Values Dialogue Box

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

Combined Duplicate Rows and Summed the Values in Excel


Download Practice Workbook

You can download our practice workbook from here for free!


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.


Further Readings



<< Go Back to Merge Duplicates in Excel | Duplicates in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shahin Mahmud
Shahin Mahmud

Hello I am Shahin Mahmud. I love working with Excel. I am a Civil Engineer holding a Bachelor’s degree from Bangladesh University of Engineering and Technology. I am interested in anything related to computers and phones. I also like to help people facing computer related problems and write articles about them.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo