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
ErrorGoTo:
ConcatenateIf = CVErr(xlErrValue)

End Function

Example

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, ", ")