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

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

sum if contains text wildcards Main Function

Sum If Text Contains

First, we will demonstrate how to sum data related to cells containing specific text using the SUMIFS Function.

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

SUMIFS syntax

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

SUMIFS Text Contains (HardCode)

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

In this example, it’s used to find all cells that include the text “Dakota”. This search is not case-sensitive, so “dakota” is considered the same as “Dakota” or “DAKOTA”. North Dakota and South Dakota contain “Dakota” and are therefore included in the sum.

Sum if Text Starts With

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

SUMIFS Text Start (HardCode)

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 Text End HardCode

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 Text One Char (HardCode)

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 Text Combine (HardCode)

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 Text Contains ~

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 Text Contains (Ref)

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 Text Combine (Ref)

Locking Cell References

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

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

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.

sum if contains text wildcards Google Function

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!