Modifying UDF for ConcatenateIF to ConcatenateIFS

[powercell99]

Good morning,

I found a great UDF that concatenates a range of data based on another range meeting 1 criteria. It works perfect, but I want to add another condition to the criteria but I cant get the wording correct.

Here is the UDF for the 1 condition:

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, _
        ConcatenateRange As Range, Optional Separator As String = " / ") As Variant
    Dim i As Long
    Dim strResult As String
    On Error GoTo ErrHandler
    If CriteriaRange.Count <> ConcatenateRange.Count Then
        ConcatenateIf = CVErr(xlErrRef)
        Exit Function
    End If
    For i = 1 To CriteriaRange.Count
        If CriteriaRange.Cells(i).Value = Condition Then
            strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
        End If
    Next i
    If strResult <> "" Then
        strResult = Mid(strResult, Len(Separator) + 1)
    End If
    ConcatenateIf = strResult
    Exit Function
ErrHandler:
    ConcatenateIf = CVErr(xlErrValue)
End Function

Here is my attempt to add the additional criteria but its not working. Any suggestions?

Function ConcatenateIfs(CriteriaRange1 As Range, Condition1 As Variant, CriteriaRange2 As Range, Condition2 As Variant, _
        ConcatenateRange As Range, Optional Separator As String = " / ") As Variant
    Dim i As Long
    Dim strResult As String
    On Error GoTo ErrHandler
    If CriteriaRange1.Count <> ConcatenateRange.Count and CriteriaRange2.Count <> ConcatenateRange.CountThen
        ConcatenateIfs = CVErr(xlErrRef)
        Exit Function
    End If
    For i = 1 To CriteriaRange.Count
        If CriteriaRange.Cells(i).Value = Condition Then
            strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
        End If
    Next i
    If strResult <> "" Then
        strResult = Mid(strResult, Len(Separator) + 1)
    End If
    ConcatenateIf = strResult
    Exit Function
ErrHandler:
    ConcatenateIf = CVErr(xlErrValue)
End Function

Any suggestions or tips would be greatly appreciated

 

[FormR]

Re: Modufying UDF for ConcatanateIF to ConcatanateIFS

Hi, maybe take a look at post#9 here..

https://www.mrexcel.com/forum/excel...parated-into-cell-if-value-higher-than-x.html

 

[powercell99]

Re: Modufying UDF for ConcatanateIF to ConcatanateIFS

Hi FormR,

thanks for the info and suggestion. I looked at that two version of the concatIf and ConcatIfs on that link. Post 9 seemed like it might be on the right track but i cant get it to work properly (I'm getting a #NAME ?).
Also the formula =ConcatIfs($H8:$H1504,"<3",">2",$B8:$B1504, ",") is saying look at one column for two separate criteria (if value in H is less than 3 and greater than 2, then concatenate col B)

What I need it to do is if Col L val = X and Col B val = Y then concatenate col A with a deliminator of “, “ or “ / “


I like the logic of the SumIFs or the CountIFs for building the formula, but i cant get the UDF right.

Any suggestions?

 

[powercell99]

Re: Modufying UDF for ConcatanateIF to ConcatanateIFS

Hi FormR,

Thanks again for your help and time. I was able to find another post that gave me exactly what i need .

https://www.mrexcel.com/forum/excel...criteria-cells-match.html?highlight=concatifs
(Reply #2 ) did the job.

Thanks again for the suggestions.