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

- Written by Puneet

To sum values from cells where you have a specific text or a partial text, you need to use wildcard characters with SUMIF.

In the criteria argument, you need to use an asterisk before and after the text using an ampersand, just like we have used in the following example to sum cells where you have the text “Mobile” in the “Product” column.

In this tutorial, we will learn to write this formula by using SUMIF.

## Formula to Sum IF Cell Contains a Specific Text

1. First, in cell C1, enter “=SUMIF(“.
2. After that, refer to the range from which we need to check the criteria.
3. Now, in the criteria, enter an asterisk-criteria-asterisk (“*”&”Mobile”&”*”).
4. Next, in the sum_range argument, refer to the quantity column.
5. In the end, hit enter to get the result.
`=SUMIF(A2:A10,"*"&"Mobile"&"*",B2:B10)`

The formula looks at each cell in the range A2 through A10 and checks if the word “Mobile” appears anywhere in each of these cells.

The “*” wildcard character on both sides of “Mobile” tells Excel to ignore what comes before or after it, which makes it a partial text.

And apart from this, SUMIFS can be used if you have multiple conditions.

`=SUMIFS(sum_range, criteria_range1, "*text*")`

## SUM IF Cell Contains Partial Text using Case-Sensitive

If you want to sum values based on a case-sensitive search, use an array formula combining FIND, IF, and SUM. This formula allows for a manual array setup to handle case-sensitive partial text match.

`=SUM(IF(ISNUMBER(FIND("MOBILE", A2:A10)), B2:B10, 0))`

Note – For this formula to work as an array formula in some versions of Excel, you need to enter it with Ctrl+Shift+Enter.

This formula sums values from column B, but only for column A rows containing the exact word “MOBILE” with the same uppercase letters.

Here’s how it works: The formula goes through each cell in the range A2 to A10 and uses the FIND function, which is case-sensitive, to look for the value “MOBILE”.

If “MOBILE” is found, FIND returns a number indicating where “MOBILE” starts in the text. The ISNUMBER function checks if FIND returned a number (meaning “MOBILE” was found). If it did find “MOBILE”, the corresponding value from column B is included in the sum.

If “MOBILE” wasn’t found, or if it’s in a different case (like “Mobile” or “mobile”), it adds zero instead for that row. This way, the formula only adds up the numbers from column B where “MOBILE” exactly matches in column A.

## Use Dynamic Functions to Sum Values on a Partial Text

Yes, you can utilize Excel’s dynamic array functions to sum if a partial text matches more dynamically and efficiently, especially with newer versions of Excel that support these features.

Let’s say, column A contains product names, and column B contains corresponding sales figures. You want to sum all sales values where the product name contains “Mobile”:

`=SUM(FILTER(B:B, ISNUMBER(SEARCH("Mobile", A:A))))`

This formula sums up values from column B, but only for rows where the word “Mobile” is found in column A.

It searches for “Mobile” in each cell of column A and, if found, adds the corresponding value from column B in the total. If “Mobile” isn’t found in any cell, or if there’s an error, the formula returns 0.

But if no cells meet the criteria, FILTER will show an error, which you can handle with IFERROR:

`=IFERROR(SUM(FILTER(B:B, ISNUMBER(SEARCH("Mobile", A:A)))),0)`

Or you can also go with BYROW in combination with SEARCH.

`=SUM(BYROW(A:A, LAMBDA(cell, IF(ISNUMBER(SEARCH("Mobile", cell)), INDEX(B:B, ROW(cell)), 0))))`

This formula goes through column A’s row to check if it contains the word “Mobile”. If the word “Mobile” is found in a row, the formula looks at the corresponding row in column B and takes that sales value.

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

And if you like to use a custom function for this well, 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. And then, choose Insert > Module. This action creates a new module. Paste it into the newly created module.

Now, you can now use this new function: iSUMIFCONTAINS

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

And if you want to do sum for a partial text based using case-sensitive:

```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)`
Last Updated: April 29, 2024