CODEWITHSUNDEEP
excel
cardern
cardern

Reputation: 725

SumIf for strings

Am I able to use the SumIf function for strings? If not, is a UDF the only alternative or is there a way to accomplish this through index somehow potentially?

I am trying to concatenate the values of a table if a cell in the corresponding row is set to "Yes".

Example:

Chicago ----------- Yes
New York ---------- (blank)
Los Angeles ------ Yes
Memphis ---------- Yes
San Francisco --- (blank)

Result: Chicago, Los Angeles, Memphis

Upvotes: 2

Views: 4567

Answers (1)

Stepan1010
Stepan1010

Reputation: 3136

Concatenating is different than summing when it comes to character encoding (ASCII). SumIf is for summing you can't use it to concatenate. It is important to understand the difference.

I recommend this UDF from a one Mr. Mike Rickson:

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, _
    Optional ByVal stringsRange As Range, Optional Delimiter As String) As String
    Dim i As Long, j As Long, criteriaMet As Boolean

    Set compareRange = Application.Intersect(compareRange, _
    compareRange.Parent.UsedRange)

    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
    stringsRange.Column - compareRange.Column)

    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function

Usage:

1

2

Upvotes: 3

Related Questions