How to Consolidate Rows and Sum Data in Excel?

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.

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.

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

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

Here are the consolidated rows and summed data.

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

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

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

• You can see the customer names without duplicates.

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

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.

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

Finally, here is the result.

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.

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

• It will create a new Pivot Table.

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

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

• In the module window, copy the following VBA code and click on the Run button (or just press F5 to run the 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.

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

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