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