3 Ways to Combine Duplicate Rows and Sum Values in Excel

Sometimes we need to combine duplicate rows and sum the values in Excel. In this article, we are going to show you how to combine duplicate rows and sum the values in Excel with 3 easy approaches. We have also included some relevant concerns at the end of this article. So, let’s get started.

Contents hide

📂 Download Excel File

Learn to Combine Duplicate Rows and Sum Values in Excel with 3 Approaches

1. Applying Consolidate Feature

2. Using Remove Duplicates Option with SUMIF Function

3. Adopting Pivot Table to Combine Duplicate Rows and Sum Values in Excel

How to Consolidate Rows in Excel Based on Criteria

📄 Important Notes

📝 Takeaways from This Article

Conclusion

📂 Download Excel File

Download the free workbook from here:

How to Combine Duplicate Rows and Sum Values.xlsx

Learn to Combine Duplicate Rows and Sum Values in Excel with 3 Approaches

While writing this article, “How to Combine Duplicate Rows and Sum the Values in Excel ”, we’ve considered a dataset that contains approximately 3 columns and 15 rows. In this dataset, we added Name, and Points to make you better understand. But if you want, you can change the entities and put your own values.

APPROACH

1. Applying Consolidate Feature

Using the Consolidate option in Excel is one of the simplest ways to combine duplicate rows and sum the values. It is very easy and straightforward. You can accomplish this by simply following the instructions.

⬇️⬇️ STEPS ⬇️⬇️

Begin by selecting cell E6.
Secondly, navigate to the Data tab.
Belatedly, select the Consolidate option.

You will now see a dialogue box labeled Consolidate.
At Function, select Sum.
Assign the Reference as-

$B$6:$C$15

Use labels in will be the Left column.
Finally, press the OK button.

Consequently, you will get the outcome.

This is, without a doubt, a very simple method for combining duplicate rows and summing the values in Excel.

APPROACH

2. Using Remove Duplicates Option with SUMIF Function

It is very simple to remove duplicates to combine duplicate rows and sum the values in Excel using the SUMIF function. It is, in fact, one of the simplest methods. You can accomplish this by following the steps below.

⬇️⬇️ STEPS ⬇️⬇️

To begin, select the entire E column.
Secondly, go to the Data tab.
Belatedly, select Remove Duplicates.

You will now see a dialog box titled Remove Duplicates.
Choose My data has headers.
Choose a Name from the Columns.
Subsequently, click OK.

You’ll notice that all of the duplicate names have been removed.

Fifthly, go to cell F6.
Afterward, enter the following formula into that cell.

=SUMIF(B$6:B$15,E6,C$6:C$15)

Sixthly, tap Enter.

Finally, you will see the desired outcome.

This is a very useful method for combining duplicate rows and summarising the values in Excel.

APPROACH

3. Adopting Pivot Table to Combine Duplicate Rows and Sum Values in Excel

Using an Excel Pivot Table is another quick way to combine duplicate rows and sum the values in that spreadsheet. It is simple to accomplish. You only need to follow the steps below.

⬇️⬇️ STEPS ⬇️⬇️

Choose cell B6.
Secondly, select the Insert tab
Select PivotTable, followed by From Table/Range.

Now you will get a dialogue box named PivotTable from table or range.
Select the Table/Range and put the following value.

‘Approach 2’!$B$5:$C$15

Thirdly, select Existing Worksheet.
Select Location and write the given value.

‘Approach 2’!$E$5

Fourthly, Now press OK.

Now you will get PivotTable1.

Now, on PivotTable1, click anywhere.

PivotTable Fields are located on the right side of the worksheet.
Finally, choose NAMES and Points.
Select Name under Rows and Sum of Points under Values.

Eventually, The necessary outcomes will materialize.

Undoubtedly, this method is very special to combine duplicate rows and sum the values in Excel.

How to Consolidate Rows in Excel Based on Criteria

You’ve come to the right place if you’re looking for the simplest ways to consolidate rows in Excel based on criteria. In this section, I will attempt to demonstrate how to consolidate rows in Excel based on criteria.

⬇️⬇️ STEPS ⬇️⬇️

Primarily, select cell F6. Then it would help if you first used the following function. AMPERSAND operator and COUNTIF function were used in this case.

=B6&” “&COUNTIF($B$6:$B6,B6)

Here the function refers to COUNTIF(criteria range, criteria), and & is the summation of text and space.
Secondly, the following result will show up after entering the formula and dragging it all to the Combined column.

Then, on cell G6, perform the following logical function.

=IF($B6&” “&1=F6, INDEX(C:C,MATCH($B6&” “&1,F:F,0))&” “&IFERROR(INDEX(C:C,MATCH($B6&” “&2,F:F,0)),””)&” “&IFERROR(INDEX(C:C,MATCH($B6&” “&3,F:F,0)),””)&” “&IFERROR(INDEX(C:C,MATCH($B6&” “&4,F:F,0)),””),””)

🔨 Formula Breakdown

Here:

👉 INDEX(C:C,MATCH($B6&” “&4,F:F,0)) is the main logic here. $B6 is the cell reference and & summarizes the text and space. The INDEX function extracts the values of the C column corresponding to the B6 cell and returns it if the values match or not.

👉 IFERROR(INDEX(C:C,MATCH($B6&” “&3,F:F,0)),””) means IFERROR returns the formula’s result unless the formula evaluates to an error, in which case it returns a value. Here the formula will work for the F column.

👉 IF($B6&” “&1=F6, INDEX(C:C,MATCH($B6&” “&1,F:F,0)) will give us the result. If the formula matches with the corresponding cell then it will show us the result, otherwise, the cell will remain empty.

Now tap Enter.
This will lead to the desired result.

Finally, this approach to consolidating rows in Excel according to criteria is very helpful as IF, INDEX, IFERROR, and MATCH functions have been used here.

📄 Important Notes

You should be aware of the following things while performing the processes mentioned above:

🖊️ You should be cautious when using the Consolidate option.

🖊️ While applying the Excel Pivot Table, you need to be conscious about selecting the cell.

🖊️ You should exercise caution when using the Excel functions.

📝 Takeaways from This Article

If we summarize the whole article, we have got some points.

📌 Firstly, we’ve used the Consolidate option to combine duplicate rows and sum the values in Excel.

📌 Secondly, we adopted the Removing Duplicates option and used the SUMIF function.

📌 Thirdly, we applied the Excel Pivot Table.

📌 Finally, we’ve tried to show you how to consolidate duplicate rows in Excel based on criteria.

Conclusion

We wish that you would be able to combine duplicate rows and sum the values in Excel using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If there is any skepticism in your mind, feel free to ask me in the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website www.ExcelDen.com.

(Visited 79 times, 1 visits today)