Consolidate Rows and Sum Data in Excel (4 Effective Methods)

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.


Download Practice Workbook

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


4 Ways to Consolidate Rows and Sum Data in Excel

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 apply the Consolidate function for consolidating 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 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

Read More: How to Consolidate Data from Multiple Rows in Excel (4 Quick Methods)


2. Using SUMIF Function Together with 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

Read More: How to Remove Consolidation in Excel (2 Handy Methods)


Similar Readings


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

Read More: How to Consolidate Multiple Worksheets into One PivotTable (2 Methods)


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

Read More: [Fixed]: Consolidation Reference Is Not Valid in Excel (with Quick Fix)


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. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below


Related Articles

Hafiz Islam
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo