Here’s an overview of concatenating cells if they meet certain criteria.

**How to Concatenate Multiple Cells Based on Criteria in Excel: 4 Easy Ways**

**Method 1 – Combine Multiple Cells Based on Criteria with Formula and a Filter**

We have the following dataset containing colors with codes. We’ll concatenate the **Color** cells based on their codes.

**Steps**

- Enter the following formula in cell
**D5**:

`=IF(B6<>B5,C6,D5&","&C6)`

- Use the
**fill handle**tool to apply this formula to the cells below.

- Apply the following formula in cell
**E5**:

`=IF(B6<>B7,CONCAT(B6,",""",D6,""""),"")`

- Select cell
**E4**. - Select
**Sort & Filter**and choose**Filter**from the**Data**tab as shown in the following picture.

- The
**Filter**column will look as follows.

- Click on the drop-down arrow in cell
**E4**. - Uncheck (
**Blanks**). - Click
**OK**.

- The
**Color**cells will be concatenated as follows.

**Read More: **Concatenate Multiple Cells but Ignore Blanks in Excel

**Method 2 – Concatenate Multiple Cells Based on Criteria with the CONCAT Function**

**Steps**

- Modify the earlier dataset as follows.

- Enter the following formula in cell
**F5**:

`=CONCAT(IF(C5:C16=E5,B5:B16,""))`

- Enter a color name in cell
**E5**. We put**Yellow**for the example. - The result will look as follows.

- Change the
**Color**name and see how the result changes.

**Read More:** How to Concatenate Multiple Cells in Excel

**Method 3 – Join Multiple Cells Based on Criteria with the TEXTJOIN Function**

**Steps**

- We have a dataset with Student IDs and Marks.

- We want the student IDs for those who got more marks than the criteria (E5) concatenated in cell
**F5**. - Enter the criteria in cell
**E5**.

- Apply the following formula in cell
**F5**.

`=TEXTJOIN(",",TRUE,IF($C$5:$C$16>E5,$B$5:$B$16,""))`

- You will see the
**ID**s concatenated as follows.

- You can change the criteria value as well as the criteria in the formula.
- To find student IDs for students who got equal to or lower marks than the condition, use:

`=TEXTJOIN(",",TRUE,IF($C$5:$C$16<=E5,$B$5:$B$16,""))`

**Read More: **Combine Multiple Cells into One Separated by Comma in Excel

**Method 4 – Concatenate Multiple Cells Based on Criteria with a UDF**

**Steps**

- Consider the following dataset similar to the earlier one.

- Enter the following formula in cell
**E5**.

`=UNIQUE(C5:C16,FALSE,FALSE)`

- This gives the list of unique marks obtained by the students.

- Press
**Alt + F11**to open the**Microsoft Visual Basic for Applications**. You will also find it in the**Developer**tab. - Select
**Insert**and choose**Module**.

- Copy the following code and paste it on the blank module.

```
Function CONCATIF(Criteria As Range, Concatcriteria As Variant, ConcatRange As Range, Optional Delimiter As String = ",") As Variant
Dim Results As String
On Error Resume Next
If Criteria.Count <> ConcatRange.Count Then
CONCATIF = CVErr(xlErrRef)
Exit Function
End If
For j = 1 To Criteria.Count
If Criteria.Cells(j).Value = Concatcriteria Then
Results = Results & Delimiter & ConcatRange.Cells(j).Value
End If
Next j
If Results <> "" Then
Results = VBA.Mid(Results, VBA.Len(Delimiter) + 1)
End If
CONCATIF = Results
Exit Function
End Function
```

- Select
**Close and Return to Microsoft Excel**from the**File**tab.

- Apply the following formula in cell
**F5**and drag it to the cells below.

`=CONCATIF($C$5:$C$16,E5,$B$5:$B$16,",")`

- You will see the result as follows.

**Read More: **How to Merge Cells Using Excel Formula

**Things to Remember**

**The CONCATENATE function**is an earlier version to the**CONCAT**function. Both functions give the same result.- The
**TEXTJOIN function**may only be available in the newer versions of**Microsoft Excel**.

**Download the Practice Workbook**

**Related Articles**

- How to Combine Cells with Same Value in Excel
- How to Combine Cells into One with Line Break in Excel
- How to Combine Two Cells in Excel with a Dash
- How to Merge Multiple Cells without Losing Data in Excel
- How to Merge Cells Vertically Without Losing Data in Excel

**<< Go Back To Excel Concatenate Multiple Cells | Concatenate Excel | Learn Excel**

Hi Md

Can this concatenate multiple cells based on criteria be done in power query? I am using a custom function in excel as per the 4th solution however I have 10’s of thousands of rows and it takes so long

Thanks GT

Hello Grant!

Yes, you can do that. But if you gave a little more description of your dataset, it would’ve been easier for me to help you. Anyway, you can apply the following steps in Power Query for the dataset used in the 4th solution in the article.

Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content]

Changed Type = Table.TransformColumnTypes(Source,{{“Roll”, Int64.Type}, {“Marks”, Int64.Type}})

Reordered Columns = Table.ReorderColumns(#”Changed Type”,{“Marks”, “Roll”})

Sorted Rows = Table.Sort(#”Reordered Columns”,{{“Marks”, Order.Ascending}})

Transposed Table = Table.Transpose(#”Sorted Rows”)

Merged Columns = Table.CombineColumns(Table.TransformColumnTypes(#”Transposed Table”, {{“Column1”, type text}, {“Column2”, type text}, {“Column3”, type text}, {“Column4”, type text}}, “en-US”),{“Column1”, “Column2”, “Column3”, “Column4”},Combiner.CombineTextByDelimiter(“,”, QuoteStyle.None),”Merged”)

Merged Columns1 = Table.CombineColumns(Table.TransformColumnTypes(#”Merged Columns”, {{“Column5”, type text}, {“Column6”, type text}, {“Column7”, type text}}, “en-US”),{“Column5”, “Column6”, “Column7”},Combiner.CombineTextByDelimiter(“,”, QuoteStyle.None),”Merged.1″)

Merged Columns2 = Table.CombineColumns(Table.TransformColumnTypes(#”Merged Columns1″, {{“Column8”, type text}, {“Column9”, type text}, {“Column10”, type text}}, “en-US”),{“Column8”, “Column9”, “Column10”},Combiner.CombineTextByDelimiter(“,”, QuoteStyle.None),”Merged.2″)

Merged Columns3 = Table.CombineColumns(Table.TransformColumnTypes(#”Merged Columns2″, {{“Column11”, type text}, {“Column12”, type text}}, “en-US”),{“Column11”, “Column12”},Combiner.CombineTextByDelimiter(“,”, QuoteStyle.None),”Merged.3″)

Transposed Table1 = Table.Transpose(#”Merged Columns3″)

Split Column by Delimiter = Table.SplitColumn(#”Transposed Table1″, “Column1”, Splitter.SplitTextByDelimiter(“,”, QuoteStyle.Csv), {“Column1.1”, “Column1.2”, “Column1.3”, “Column1.4″})

Changed Type1 = Table.TransformColumnTypes(#”Split Column by Delimiter”,{{“Column1.1”, Int64.Type}, {“Column1.2”, Int64.Type}, {“Column1.3”, Int64.Type}, {“Column1.4″, Int64.Type}})

Removed Columns = Table.RemoveColumns(#”Changed Type1”,{“Column1.2”, “Column1.3”, “Column1.4″})

Renamed Columns = Table.RenameColumns(#”Removed Columns”,{{“Column1.1”, “Marks”}, {“Column2”, “Roll”}})

Hi

Hello –

Thank you for this article. It is really helpful for what I am trying to do. I do have a question:

Is it possible to use a range criteria to get multiple values in a single cell?

For example I have the follwoing table:

ROUTE MILE #OFLANES

10 15 2

10 20 2

10 25 3

10 30 3

10 35 1

The criteria is to inpute a range of miles and to extract the number of lanes found within that range.

If I chose miles 20-30, I would like to see “2,3,3” in a single cell. If I chose miles 25-35, I would like to see “3,3,1” in a single cell.

I am thinking the TEXTJOIN example you gave would be the idea function but I am unsure how to tell the IF function to look inside a range.

Hope you can help.

Thanks

Joe

Hi Joe,

Assume your example data table (with headers) starts from cell A1. The lower criteria range i.e 20 is in cell E2 and the upper criteria range i.e 30 in cell G2. Now enter the following formula in cell H2 to get the desired result.

`=TEXTJOIN(",",TRUE,IF($B$2:$B$6>=$E$2,IF($B$2:$B$6<=$G$2,$C$2:$C$6,""),""))`

You can change the criteria ranges as required. For example, change the lower criteria from 20 to 25 and the upper criteria from 30 to 35. This will give you the #OFLANES values for the range 25-35. You can create dropdown lists in the criteria cells E2 and G2 to easily change the criteria value.

I’ve also emailed you an excel document with the solution. Please check.

Don’t hesitate to let us know if you face any further problems. Thanks for reaching out to us.

Regards,

Md. Shamim Reza (ExcelDemy Team)

Thank you very much! This is exactly what I was looking for.