How to Consolidate Rows and Sum Data in Excel?

Get FREE Advanced Excel Exercises with Solutions!

It may be easy to find multiple duplicate rows in the data when analyzing it in Excel. In such a situation, maybe you should quickly consolidate the rows and sum the according values in Excel. Manually doing this will be a hassle. In this article, you will learn 4 handy ways to consolidate rows and sum data in Excel.


How to Consolidate Rows and Sum Data in Excel: 4 Effective Methods

In an Excel worksheet, I have a number of duplicate entries, as shown in the screenshot below. Thus, I want the duplicate rows consolidated, and the values summed in another column.

Sample Data to Consolidate Rows and Sum Data in Excel


1. Use Consolidate Feature in Excel

To do consolidation in Excel, we will use the Consolidate feature. We will consolidate rows and sum data in Excel, just follow the steps below.

📌 Steps:

  • First, copy the headers of the source data and paste them into the spot where you want the consolidated data to appear.

Use Consolidate Feature to Consolidate Rows and Sum Data in Excel

  • Then, select the cell (F5) just below the Customer header. Next, go to the Data tab >> Data Tools >> Consolidate. A Consolidate window will appear.

Use Consolidate Feature to Consolidate Rows and Sum Data in Excel

  • You can now select Sum (it should already be there) from the Functions dropdown box. Use the mouse to select the cells without headers in the Reference box (this is very important) or manually enter the range of cells (do not forget to use $ to make the range absolute, for example, $C$5:$D$17. After that, make sure to check the box next to the Left Column. And finally, click OK.

Use Consolidate Feature to Consolidate Rows and Sum Data in Excel

Here are the consolidated rows and summed data.

Use Consolidate Feature to Consolidate Rows and Sum Data in Excel


2. Using the SUMIF Function Together with the Remove Duplicates Tool

Follow the steps below one by one to apply the SUMIF function with the Remove Duplicates tool properly to consolidate rows and sum data in Excel.

📌 Steps:

  • To begin with, copy the entire Customer column with the header (C4:C17) and paste it where you want to place the consolidated rows and summed data (F4:F17).

Using SUMIF Function together with Remove Duplicates Tool

  • Now, after selecting the copied cells, go to Data Tab >> Data Tools >> Remove Duplicates. A Remove Duplicates dialog box will pop up.

Using SUMIF Function together with Remove Duplicates Tool

  • Tick the box that says My Data has headers. Press OK after selecting the columns (in our case, Customer).

Using SUMIF Function together with Remove Duplicates Tool

  • You can see the customer names without duplicates.

Using SUMIF Function together with Remove Duplicates Tool

  • You now need to add a new header next to Customer named Total Due. Under the new header, select Cell G5 and enter the following formula, and then, press ENTER.
=SUMIF(C$5:C$17,F5,D$5:D$17)

Here, the summation value of F5 is calculated based on the data within D$5:D$17 that correspond to the names within C$5:C$17.

Using SUMIF Function together with Remove Duplicates Tool

  • Now, drag the Fill Handle to the remaining cells to get the summed data for all the individual customers.

Using SUMIF Function together with Remove Duplicates Tool

Finally, here is the result.

Using SUMIF Function together with Remove Duplicates Tool


3. Use Excel Pivot Table to Consolidate Rows and Sum Data

Pivot Tables are Excel’s all-purpose feature. Among the many things we can do with PivotTables is consolidate information and remove duplicates. To apply this amazing feature, follow the steps below.

📌 Steps:

  • Choose an empty cell where you want to create the pivot table. Then, go to the Insert tab >> Tables >> Pivot Table. A PivotTable from table or range dialog box will pop up.

Use Pivot Table to Consolidate Rows and Sum Data

  • Under the Select a table or range section, select the range (In this case, C4:D17). As the pivot table can be used to get data from different worksheets, a new term for the sheet name will also appear in the box automatically. In this example, it is ‘3. Pivot Table’!$C$4:$D$17 for selecting cells C4:D17 in 3. Pivot Table sheet.
  • To get the output in the cells of the current worksheet, select Existing Worksheet and in the Location select a cell with the mouse or write ‘Worksheet Name’!Cell Id (In this example, 3. Pivot Table’!$F$4). Finally, press OK.

Use Pivot Table to Consolidate Rows and Sum Data

  • It will create a new Pivot Table.

Use Pivot Table to Consolidate Rows and Sum Data

  • In the PivotTable Fields, put the check mark on the Customer and Due box. Our pivot table now shows the sum of dues for all customers.


4. Use a VBA Code to Consolidate Rows and Sum Data

Follow the steps below carefully to run the VBA code to consolidate rows and sum data in Excel properly.

📌 Steps:

  • In the beginning, select the range (In this example, C4:D17). Then, go to Developer >> Visual Basic. Or press the ALT+F11 keys to open the Microsoft Visual Basic for Applications window.

Use a VBA Code to Consolidate Rows and Sum Data in Excel

  • Now, click on the Insert >> Module as shown in the following image. A module window will appear.

Use a VBA Code to Consolidate Rows and Sum Data in Excel

  • In the module window, copy the following VBA code and click on the Run button (or just press F5 to run the code).

Use a VBA Code

Sub ConsolidateRowsAndSumData()
Dim WorksheetRng As Range
Dim Dict As Variant
Dim arr As Variant
On Error Resume Next
xTitleId = "ExcelDemyforExcel"
Set WorksheetRng = Application.Selection
Set WorksheetRng = Application.InputBox("Range", xTitleId, WorksheetRng.Address, Type:=8)
Set Dict = CreateObject("Scripting.Dictionary")
arr = WorksheetRng.Value
For j = 1 To UBound(arr, 1)
Dict(arr(j, 1)) = Dict(arr(j, 1)) + arr(j, 2)
Next
Application.ScreenUpdating = False
WorksheetRng.ClearContents
WorksheetRng.Range("C1").Resize(Dict.Count, 1) = Application.WorksheetFunction.Transpose(Dict.keys)
WorksheetRng.Range("D1").Resize(Dict.Count, 1) = Application.WorksheetFunction.Transpose(Dict.items)
Application.ScreenUpdating = True
End Sub
  • After running the code, you need to type the range that you wish to consolidate rows and sum data in the prompt box. And then, click OK.

Use a VBA Code to Consolidate Rows and Sum Data in Excel

Here, duplicate rows are consolidated and values are summed up.

Use a VBA Code to Consolidate Rows and Sum Data in Excel


Download Practice Workbook

You can download the following practice workbook that we have used to prepare this article


Conclusion

In this tutorial, I have discussed 4 handy ways to consolidate rows and sum data in Excel. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below


<< Go Back To Consolidation in Excel | Merge Sheets in Excel | Merge 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.
Hafizul Islam
Hafizul Islam

Hafizul Islam is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo