Sum if Cell Contains Specific Text Using Wildcards – Excel & Google Sheets

This tutorial will demonstrate how to use wildcards with the SUMIFS Function to sum data corresponding to cells that contain specific text in Excel and Google Sheets.

 

Sum If Text Contains

The SUMIFS Function sums data rows that meet certain criteria. Its syntax is:

This example will sum all Scores with a State Name that contains “Dakota” using the SUMIFS Function and the * wildcard character.

=SUMIFS(C3:C9,B3:B9,"*Dakota*")

The * character allows for any number (including zero) of other characters to take its place.

This example finds all cells that include “Dakota”. This search is not case-sensitive, so “dakota” is considered the same as “Dakota” or “DAKOTA”.

Sum if Text Starts With

The * character can also be used to look for cells that start with the specified text:

=SUMIFS(C3:C9,B3:B9,"New*")

New York, New Jersey, and New Mexico start with “New” and are therefore included in the sum. Note that, with the “New*” search term, the text cell must start with “New”; simply containing those characters is not enough.

Sum if Text Ends With

Similarly, we can sum all Scores for States ending in “o” using:

=SUMIFS(C3:C9,B3:B9,"*o")

New Mexico and Ohio end with “o” and are therefore included in the sum.

Using the ? Wildcard Character

The ? character can be used to represent any single character in a text string.

This example finds all State Names starting with “New”, followed by exactly 7 characters (including spaces).

=SUMIFS(C3:C9,B3:B9,"New???????")

New Jersey and New Mexico meet these criteria, but New York does not, since there are only 5 characters following “New” in New York.

Note that * and ? wildcard characters can be combined if required to make very specific search commands. This next example finds State Names that start with “N” and contain an “o” before the last character of the string. This excludes New Mexico; it starts with “N” but does not have an “o” before the last character.

=SUMIFS(C3:C9,B3:B9,"N*o?*")

This functionality is particularly useful when searching through product codes, ZIP codes, or serial numbers where the position of each character has a specific meaning.

Using the ~ (Tilde) character

The special character ~ (known as a tilde) allows us to treat the * or ? characters as if they are simple text values and do not behave as wildcards.

In the example below, we need to sum the Stock Level when the Product Name specifically matches the text “Product ?”:

=SUMIFS(C3:C8,B3:B8,"Product ~?")

The ~ immediately before a * or a ? character turns it back into a text value, so the search term “Product ~?” is used to find an exact text match to “Product ?”.

Combining SUMIFS Wildcards with Cell References

Usually, it is not good practice to hard-code values into formulas. Instead, it is more flexible to use separate cells to specify values for our search terms.

To search for whether the cells contain the text held in cell E3, we can use the SUMIFS Function with a cell reference and * wildcards:

=SUMIFS(C3:C9,B3:B9,"*"&E3&"*")

Note that the text “Dakota” has been replaced by the cell reference &E3& and the * characters have been put into quotes (” “).

Multiple cell references and wildcard characters can also be combined together. To find State Names that start with the text in cell E3 and contain the text in cell F3 followed by at least 1 more character, the following formula can be used:

=SUMIFS(C3:C9,B3:B9,E3&"*"&F3&"?*")

Locking Cell References

To make our formulas easier to read, we’ve shown the formulas without locked cell references:

=SUMIFS(C3:C9,B3:B9,"*"&E3&"*")

But these formulas will not work properly when copy and pasted elsewhere in your file. Instead, you should use locked cell references like this:

=SUMIFS($C$3:$C$9,$B$3:$B$9,"*"&E3&"*")

Read our article on Locking Cell References to learn more.

Sum if Cell Contains Specific Text Using Wildcards in Google Sheets

These formulas work exactly the same in Google Sheets as in Excel.