When you’re looking up text or values you’ll often want to use wildcards in your searches.

The wildcards are:

• * (Asterisk)
• ? (Question Mark)
• <> (Less Than, Greater Than)
•  (blank)

Wildcards work with all of the following functions:

• SUMIF, SUMIFS
• COUNTIF, COUNTIFS
• AVERAGEIF, AVERAGEIFS
• VLOOKUP, HLOOKUP (‘<>’ will not work with these)
• MATCH (‘<>’ will not work with this)

Lets look at the wildcard combinations using the SUMIF function. The arguments for SUMIF are: RANGE, CRITERIA and SUM_RANGE.  RANGE is where we’re going to look for our criteria. CRITERIA is what we’re looking for (this will include our wildcards). SUM_RANGE is the range of data to sum when the criteria is matched in the range.

Below we look at these examples: “” , “*” , “<>*” , “*A*” , “<>*A*” , “???” , “A?” , “*300*” , “<>B” , “<>A?” , “<>” , “<>???”

To start, check Fig(a1) for the table we’ll use when analyzing the wildcard results. This table shows also shows the results for SUMIF with 300 as the criteria.

It is worth noting that the result includes cell B22 even though this is formatted as text. This might be surprising so it is worth being aware of. Other than that, as expected this sums all cells in the range that equal 300. It doesn’t matter if cell B26 is formatted as text or a number, the result of the SUMIF is unaffected.

Now lets look at the wildcard examples.

1. SUMIF Blank [criteria “” = BLANK cells]

If we use a blank criteria the SUMIF will sum all the blanks (cell B9 in this case).

COUNTIF works in the same way with BLANK criteria. This may be useful to identify any blank cells in a list. You may want to consider this as a validation check in your spreadsheets. Either to check users have entered all relevant cells. Or to check that data you’ve imported to Excel is complete.

Note: these BLANK cells could include formulas that are returning blank text. Such as =IF($A$1 = 1,  1,  “”)

2. Excel Wildcard Asterisk “*”  [criteria “*” = TEXT cells]

The asterisk is a wildcard for any text in the search for matching cells. When used on its own as in this case it returns any cell with text.

On its own it therefore excludes all blank cells (as well as excluding any numeric cells). Again this can be a useful validation. While the example above was checking for blank cells, this time we’re also excluding numeric values from the result.

The rows summed are shown in yellow.

3. Wildcard Asterisk Preceded By “<>*”  [criteria“<>*” = NON-TEXT cells]

Now if we add ‘<>’ to the asterisk wildcard we can get the opposite result. So ‘<>*’ will return a sum of all the numeric cells (including any blanks in the range).

If you want a count of numeric values only in range $B$7:$B$22 then you can use this wildcard example then deduct the blank count: “=COUNTIF($B$7:$B$22,”<>*”) – COUNTIF($B$7:$B$22,””)”

4. Combining Asterisk Wildcard With Text   [criteria“*A*” = text with ‘A’ somewhere in string]

Lets check our list for ‘*A*’. This is one of the most common uses of wildcards in Excel, particularly with VLOOKUP or SUMIF. You can see below that anything with ‘A’ is found and the range in column C is summed. This is not case sensitive.

5. Wildcard to Exclude Anything With ‘A’ in Cell   [criteria“<>*A*” = cells with no ‘A’]

Reversing the wildcard in 4. above. We just add ‘<>’ to the previous string. So ‘<>*A*’ is going to result in a sum of all cells that don’t have ‘A’ in their value. This sum includes the blank cell B9 as this also fulfills the criteria of not containing ‘A’ in its value.

6. The Question Mark Wildcard   [criteria“???” = text with exactly 3 characters length]

Again this is commonly used. While the asterisk ‘*’ represented any number of characters, the question mark ‘?’ is just going to represent a single character. In the example below we’ll use ‘???’. As it only counts text, the 3 digit numbers are not included in the result. That being the case, only cell B13 meets this criteria.

7. Another Question Mark Wildcard Example   [criteria“A?” = text which starts with ‘A’ followed by exactly one character]

In this example we’ve joined the question mark ‘?’ wildcard with text ‘A?’. As it counts as just one character the SUMIF includes only two character values that start with ‘A’. The only cell in our range that meets this criteria is cell B12.

8. Combining Asterisk ‘*’ Wildcard with A Number   [criteria“*300*” = text with ‘300’ in string]

If you’ve been following along so far it may not be surprising that ‘*300*’ is just going to pick up text with ‘300’ in the string. Any numbers with 300 are going to be excluded from the result.

9. Excluding Strings   [criteria“<>B” = text which aren’t “B”]

Lets try excluding all cells in the range that equal ‘B’. Following our logic this is simply ‘<>B’.

10. Exclude All Cells With ‘A’ Followed by One Character  [criteria“<>A?” = any cell that isn’t ‘A’ followed by one character]

Again we just need to take our string to identify ‘A’ followed by one character and precede it with ‘<>’.

11. All Non-Blank Cells   [criteria“<>” = NON-BLANKS]

We can find all non-blank cells by using the exclude ‘<>’ wildcard on its own.

12. Exclude All 3 Character Text Strings   [criteria“<>???” = cells that aren’t 3 character text]

We’re going to use ‘<>???’ in our last example. The exclusion picks up the text strings that are 3 characters long. However, numeric numbers are ignored so they are not excluded from the answer.

Work smarter in Excel with our productivity tools.

Conclusion

Hopefully these examples are enough to see the options. It is important to consider what is being excluded or included. Particularly with regard to numeric cells and blanks. As well as being powerful SUMIF criteria, these can become an excellent validation check in your spreadsheets by using COUNTIF and COUNTIFS functions.

Just to recap these examples:

1. “”  (or blank) will match blank cells
2. “*” will match any cell with text (blank cells are not matched)
3. “<>*” this is the opposite of 2. above so all numeric and blank cells meet the criteria
4. “*A*” will match any cell with ‘A’ in its value (this is not case sensitive)
5. “<>*A*” is the opposite of 4. above so you can easily exclude text from the match
6. “???” as the question mark represents a single character you can match all cell values that  are 3 characters long (provided they are text)
7. “A?” will provide all provide matches of any cell that begins with ‘A’ and is 2 characters long
8. “*300*” similar to 4. above this will match any cell formatted as text that has ‘300’ somewhere in its string.
9. “<>B” to match all cells except those that are “B”
10. “<>A?” to match all cells except those that begin “A” and are two characters long
11. “<>” all cells that are no blank
12. “<>???” all cells that are not text and 3 characters long

Learn more about use of wildcards with this example: Lookup Part of Text in a Cell

Subscribe for exclusive tips

Can you use wildcards in Excel formulas?

Yes. Wildcards work with these functions: SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, VLOOKUP, HLOOKUP, MATCH

What is the symbol for 'any' (NOT BLANK) value in Excel?

Use the asterisk (*) to represent 'any' value.

How do you use wildcards?

In an appropriate Excel function, use a wildcard to represent other characters. E.g. to count cells that start with "B" in cells A1 to A20: '=COUNTIF($A$1:$A$20,"B*")'

How do you do a wildcard search in Excel?

From the 'HOME' menu select 'Find & Select' then 'Find...'. Now type your search term using * to replace any number of characters or ? to replace single characters

What is the symbol for ANY NUMBER in Excel?

Use the less than, greater than and asterisk together (<>*) to represent 'any' numbers (includes blanks).