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.

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

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

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

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

**📕 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

**6 Suitable Ways to Convert Multiple Rows to Single Row in Excel****3 Suitable Ways to Combine Rows in Excel with Same ID**