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.


📂  Download Excel File

Download the free workbook from here:


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.

dataset to how to combine duplicate rows and sum the values in Excel

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.

using consolidate option to see how to combine duplicate rows and sum the values in Excel

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

Consolidate table

  • Consequently, you will get the outcome.

result of using consolidate option to find how to combine duplicate rows and sum the values in Excel

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

📕 Read More: 3 Ways to Convert Multiple Rows to Single Column 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 entirecolumn.
  • Secondly, go to the Data tab.
  • Belatedly, select Remove Duplicates.

using the remove duplicates how to combine duplicate rows and sum the values in Excel

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

remove duplicates table

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

using the SUMIF function how to combine duplicate rows and sum the values in Excel

  • Finally, you will see the desired outcome.

result of using the SUMIF function how to combine duplicate rows and sum the values in Excel

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

📕 Read More: 4 Quick Ways to Merge Rows with Same Value 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.

using a pivot table how to combine duplicate rows and sum the values in Excel

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

Pivot table in worksheet

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

pivot table fields in Excel worksheet

  • Eventually, The necessary outcomes will materialize.

result of using the pivot table

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.

Use of COUNTIF function to consolidate rows in Excel based on criteria

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

the result in consolidating rows based on criteria using the IF, IFERROR, INDEX, and MATCH functions

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.

📕 Read More: 6 Ways to Combine Multiple Rows in One Cell in Excel


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


Related Articles

(Visited 61 times, 1 visits today)
Jonayed

Jonayed

Hello! My name is Jonayed. I've completed my graduation from BUET in Naval Architecture & Marine Engineering. As Excel is very interesting to me, I like to do various types of research regarding Excel. My inquisitive nature drives me to find solutions to different types of problems with Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo