Hafiz Islam
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.
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.
- 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 Left Column. And finally, click OK.
Here are the consolidated rows and summed data.
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).
- 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.
=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.
- Now, drag the Fill Handle to the remaining cells to get the summed data for all the individual customers.
Finally, here is the result.
Read More: How to Remove Consolidation in Excel (2 Handy Methods)
Similar Readings
- Excel Consolidate Data from Multiple Worksheets in a Single Worksheet
- How to Consolidate Data from Multiple Ranges in Excel (2 Easy Ways)
- How to Consolidate Two Sheets into One in Excel (3 Useful Methods)
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 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.
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.
- 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.
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
- How to Consolidate Data by Category in Excel (2 Suitable Examples)
- How to Consolidate Data from Multiple Workbooks in a Single Worksheet
- How to Create a Linked Consolidation in Excel (2 Useful Methods)
- How to Build a Static Consolidation in Excel (2 Suitable Examples)
- Data Validation and Consolidation in Excel (2 Examples)
- How to Consolidate Data from Multiple Columns in Excel (7 Easy Ways)
- How to Use Grouping and Consolidation Tools in Excel (5 Easy Examples)
