Sum IF Cell Contains a Specific Text (SUMIF Partial Text)

Use a Custom Function to SUM Values Based on a Partial Text

And if you’d like to use a custom function for this, here’s a VBA function that performs a case-insensitive sum based on a partial text match:

Function iSUMIFCONTAINS(searchRange As Range, searchText As String, sumRange As Range) As Double
    Dim cell As Range
    Dim sumResult As Double

    ' Initialize the result variable.
    sumResult = 0

    ' Check if both ranges are of the same size
    If searchRange.Rows.Count <> sumRange.Rows.Count Or searchRange.Columns.Count <> sumRange.Columns.Count Then
        iSUMIFCONTAINS = CVErr(xlErrRef)
        Exit Function
    End If

    ' Loop through all cells in the searchRange
    For Each cell In searchRange
        If InStr(1, cell.Value, searchText, vbTextCompare) > 0 Then
            ' If searchText is found, add the corresponding value from sumRange
            sumResult = sumResult + sumRange.Cells(cell.Row - searchRange.Row + 1, cell.Column - searchRange.Column + 1).Value
        End If
    Next cell

    ' Return the result
    iSUMIFCONTAINS = sumResult
End Function

Press ALT + F11 to open the VBA editor. In the VBA editor, right-click on any objects in your project explorer. Then, choose Insert > Module. This action creates a new module. Paste it into the newly created module.

Now, you can use this new function: iSUMIFCONTAINS

=iSUMIFCONTAINS(A2:A10,"Mobile",B2:B10)

And if you want to do a sum for partial text based on case sensitivity:

Function iSUMIFCONTAINS(searchRange As Range, searchText As String, sumRange As Range, Optional caseSensitive As Boolean = False) As Double
    Dim cell As Range
    Dim sumResult As Double
    Dim compareMode As Integer

    ' Determine comparison mode based on case sensitivity option
    If caseSensitive Then
        compareMode = vbBinaryCompare   ' Case-sensitive
    Else
        compareMode = vbTextCompare     ' Case-insensitive
    End If

    ' Initialize the result variable.
    sumResult = 0

    ' Check if both ranges are of the same size
    If searchRange.Rows.Count <> sumRange.Rows.Count Or searchRange.Columns.Count <> sumRange.Columns.Count Then
        iSUMIFCONTAINS = CVErr(xlErrRef)
        Exit Function
    End If

    ' Loop through all cells in the searchRange
    For Each cell In searchRange
        If InStr(1, cell.Value, searchText, compareMode) > 0 Then
            ' If searchText is found, add the corresponding value from sumRange
            sumResult = sumResult + sumRange.Cells(cell.Row - searchRange.Row + 1, cell.Column - searchRange.Column + 1).Value
        End If
    Next cell

    ' Return the result
    iSUMIFCONTAINS = sumResult
End Function

=iSUMIFCONTAINS(A2:A10,"MOBILE",B2:B10,TRUE)