Using Wildcards in Criteria

So far your criteria matched a value exactly. But real data is messy. You might want every product code that starts with AB, or every job title that contains the word Manager.

Wildcards let you match partial text inside SUMIF, COUNTIF, and AVERAGEIF. Two special characters do all the work: the asterisk and the question mark.

There are exactly two:

• * the asterisk matches any number of characters, including none
• ? the question mark matches exactly one character

You place them inside the quoted criteria text. Everything you have learned about quotes and the ampersand still applies.

Put the asterisk at the end to mean any ending. To count every code in A2:A10 that starts with AB, follow AB with an asterisk.

This matches AB, AB1, ABXYZ, anything beginning with those two letters. The asterisk stands in for whatever comes after.

Put the asterisk at the start to match any beginning. To total amounts in B2:B10 where the label in column A ends with the word North, lead with an asterisk.

This catches Far North, Up North, and plain North. The asterisk absorbs whatever sits before the word.

Wrap the text in asterisks on both sides to match anywhere inside the cell. To count titles in A2:A10 that contain Manager:

This finds Sales Manager, Manager of IT, and Assistant Manager alike. The two asterisks allow any text before and after the word you care about.

The question mark matches exactly one character, no more, no less. If codes are always a letter followed by two digits, count those starting with A using one question mark per unknown character.

"A??" matches A12 and A99 but not A1 or A123, because the length must be exactly three.

To build a starts-with condition from a cell, join the cell to an asterisk with the ampersand. If D1 holds AB, match anything beginning with that text.

The & attaches the asterisk to the value in D1, producing "AB*". Now your partial match is driven by a cell instead of hardcoded text.

The same trick builds a contains search. Surround the cell with asterisks on both sides using two ampersands.

Here the value in D1 is wrapped as "*value*", so changing D1 instantly changes what the formula searches for. This is the heart of a simple search-as-you-type summary.

What if your data actually contains a real * or ? and you want to match it literally? Put a tilde ~ in front of it.

So "~?" finds a literal question mark, and "~*" finds a literal asterisk. The tilde tells the spreadsheet to treat the next character as ordinary text.

One important limit: wildcards work on text, not numbers. You cannot use * to match part of a number like a price.

If your codes are stored as real numbers, wildcards will not see them. Convert them to text first, or use comparison operators for numeric ranges instead. Wildcards and number operators solve different problems.

Imagine codes like SALES-01, SALES-02, HR-01 in column A and budgets in column B. To total the whole Sales department, match any code that starts with SALES followed by anything.

One SUMIF with "SALES*" rolls up every sub-code at once, no need to list them individually. Swap the prefix to roll up a different department.

Test your understanding of wildcard matching.

You can now match partial text in the IF math functions. Remember:

• * matches any number of characters; ? matches exactly one.
• Use "AB*" for starts-with, "*North" for ends-with, and "*Manager*" for contains.
• Join a cell with the ampersand, as in D1&"*".
• Escape a literal * or ? with a tilde, and remember wildcards only work on text.

That completes conditional math with SUMIF, COUNTIF, and AVERAGEIF.