If You are looking for some easiest ways to merge rows in Excel based on criteria, then You are in the right place. Here I will try to show the ways to merge rows in Excel based on criteria.

**Table of Contents**hide

## Download Excel Workbook

## Easiest Methods to Merge Rows in Excel Based on Criteria

Let’s get into the data table first. Here, I have taken a dataset consisting of 3 columns named ** Author**,

**,**

*Books***and 9 rows. Sometimes based on criteria multiple rows will be merged here using different types of functions.**

*Price(Tk),*

__Method-1:__ Using Consolidate Option to merge duplicate values

Suppose you have to **merge rows** on the basis of the same authors and then with respect to their name the sum of the price of their book will be added.

- To fulfill this criterion you have to first follow the procedure
**Data**tab>>**Data Tools**>>**Consolidate**

2. Then a **Consolidate **dialog box will appear where You have to select the whole data range as **Reference **and then click on **Add **on **All references **and then click on **Left Column **and finally press **OK**.

3. After that, the duplicate cells of the ** Author** column will be merged and their respective

*Price(Tk)*values will be added.

In the above scenario, I have tried to use the **SUM** function of the **Consolidate** option. But based on other criteria like counting the number of repetitions of the author names against the merged author name.

4. To solve this you have to just select the **Count Numbers** function as below.

5. After pressing **OK** the following table will appear.

**Read more:** **Merge Duplicate Rows in Excel**

__Method-2:__ Using Logical Function to merge same texts

- Here, suppose another criteria is to add all the books in a cell with respect to the same author. To solve this problem I will use the
**IF**function here. At first, you have to select thecolumn and then follow the*Author***Home**tab >>**Editing**>>**Sort & Filter**>>**Sort A to Z**

- After that, the following sorted data will appear.

- Then, use the
**IF**function as shown below

`=IF( logical test, value if true, value if false)`

Here, the **logical test** is **B3=B4 **(to check if the writers are the same)

**value if true **is to join the books of all the same writers

**value if false **is to show up the book name for a writer which is not duplicated further.

`=IF(B3=B4, E3& “” &C4,C4)`

- After that, you have to enter the formula and drag it down all the way to
. Then the following table will appear.*Formula 1*

- Then the
**IF**function is used again in the*Formula 2*

Here, `=IF(B5<>B4,0,1)`

This means if the following two cells are not equal then it will return 0 otherwise it will return 1.

- After that, enter the formula and drag it down all the way to copy the formula in
*Formula 2*

- After that, You have to filter
column and select the filter option 0 like below.*Formula 2*

- After pressing
**OK,**you will get the following table where the same books will be merged against the same author as below

__Method 3:__ Using PivotTable to merge rows

In this section, to merge rows on the basis of the same authors and then with respect to their name the sum of the price of their book the **PivotTable** option will be used.

- First You have to follow
**Insert**>>**Table**>>**PivotTable**

- Next, a Dialog Box will appear where you will have to select the data range in the
**Table/ Range**

- Then the following new sheet will appear.

- Then you will have to drag
to the*Author***Rows**field andto the*Sum of Price (Tk)***Values**After that, the following table will appear where the same rows will be merged in the**Author**column, and with respect to the authors, the prices will be added up.

__Method-4:__ Using COUNTIF to Merge Rows

In this section, I want to join all of the **Books** with respect to each** Author. **

- At first, you have to use the following function. Here,
**AMPERSAND**and**COUNTIF**have been used.**=B4&” “&COUNTIF($B$4:$B4,B4)****=COUNTIF(criteria range, criteria)**Here**&**will add these two**Texts**and**Space**

- After entering the formula and dragging up it all the way to the
**Combined**column the following result will appear.

- After that you have to use the following logical function.

=IF($B4&” “&1=E4, INDEX(C:C,MATCH($B4&” “&1,E:E,0))&” “&IFERROR(INDEX(C:C,MATCH($B4&” “&2,E:E,0)),””)&” “&IFERROR(INDEX(C:C,MATCH($B4&” “&3,E:E,0)),””)&” “&IFERROR(INDEX(C:C,MATCH($B4&” “&4,E:E,0)),””),””)

- After entering the function and dragging it through the
**Merged row of Books****name**column the following merged rows on this criterion will appear.

## Conclusion:

Hope that this article will help you to merge rows based on criteria easily. These are the easiest ways I tried to cover. Feel free to comment if anything seems difficult to understand. If You have any other ways feel free to share with us.

Hi! This article has been very helpful! Method 4 is working almost perfectly for me but I have a question: how would I have to change the logical function you provide when I am working with numbers that I want to add instead of words that get concatenated in your example.

For example your version gives me “12 1” instead of “13”.

Maybe you can help me, I am a beginner in excel… 🙂

Thank you very much!

I just figured it out by myself!

Thanks again. Have a good day!