How To Conditionally Concatenate A Range

Excel has some great built in functions for summing and counting conditionally based on given criteria, but to concatenate a range conditionally we will need to create our own user defined function.

This ConcatenateIf function will concatenate a range of values based on a given criteria and separate them with a text delimiter of your choice.

Function ConcatenateIf(CriteriaRange As Range, Criteria As Variant, _
    ConcatenateRange As Range, Optional Delimiter As String = ",") As Variant

    Dim j As Long
    Dim TempString As String
    TempString = ""

    On Error GoTo ErrorGoTo
    'Check if criteria range and concatenate range are the same size
    If CriteriaRange.Count <> ConcatenateRange.Count Then
        ConcatenateIf = CVErr(xlErrRef)
        Exit Function
    End If

    'Loop through cells in the criteria range
    For j = 1 To CriteriaRange.Count
        'Add item to the string if criteria is met
        If CriteriaRange.Cells(j).Value = Criteria Then
            TempString = TempString & Delimiter & ConcatenateRange.Cells(j).Value
        End If
    Next j

    'Remove starting delimiter
    If TempString <> "" Then
        TempString = Mid(TempString, Len(Delimiter) + 1)
    End If

    ConcatenateIf = TempString
    Exit Function
ConcatenateIf = CVErr(xlErrValue)

End Function


In this example we have a list of email addresses with a Y or N in the next column depending on if the person is planning to attend the national dinosaur convention. Now we want to get a comma separated list of the email addresses for those who plan to attend.

We use the formula:

=ConcatenateIf(C2:C10,B12,B2:B10, ", ")


About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.


Subscribe for awesome Microsoft Excel videos 😃

John MacDougall

I’m John, and my goal is to help you Excel!

You’ll find a ton of awesome tips, tricks, tutorials, and templates here to help you save time and effort in your work.

Related Posts



Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃