Excel Wildcards in your SUMIF, COUNTIF and VLOOKUP

Wildcards *?~….what am I talking about? And no, it’s not an expletive. Let’s start with an example.

COUNTIF using Wildcards

What say you wanted to count the number of cells containing the word ‘apple’ in this table.

You could simply use a wildcard (an asterisk, *, is a wildcard in Excel) in your COUNTIF formula like this:

=COUNTIF(A5:A9,”*apples*”)

Your result will be 4.

Notice that the wildcard search is not case sensitive and it will count any instance of the word, even where it’s not a word on its own like in the case of ‘Pineapples’.

Alternatively, if you wanted to reference a cell instead of typing in the word ‘apples’ your formula would be:

=COUNTIF(A5:A9,”*”&A1”*”) Where cell A1 contains the word ‘apples’.

All we are doing here is adding an asterisk to the word ‘apples’ in A1 using the ampersand. More on using the ampersand (&) to join text.

SUMIF using Wildcards

The SUMIF formula in cell B23 is:

=SUMIF(A17:A21,”*apples*”,B17:B21)

VLOOKUP Formula using Wildcards

In the table below is a list of Car Manufacturers and their location. We’ve named the range of this table car_manufacturers.

In column B of the table below we want to find the location of the car manufacturer but we only want to type in the short name for the manufacturer into Column A. e.g. ‘Ford’ instead of ‘Ford Motor Company of Australia’.

In cell B29 our VLOOKUP formula is:

=VLOOKUP(“*”&A29&”*”,car_manufacturers,2,FALSE)

Again, we’ve used the ampersand to add wildcards around ‘Ford’ in cell A29 before we look it up in our car_manufacturers table.

The Final Word on Wildcards

The other Wildcards you can use are the question mark ? and tilde ~.

The asterisk we’ve used already allows you to search for a string of text, but if you only want to search for one variable you can use the question mark wild card like this:

? Wildcard

=COUNTIF(G10:G13,”apple?”) gives a result of 3.

Notice it will find words ending in ‘s’ like ‘apples’ but won’t find ‘apple’ because the ? is a place holder for another character.

~ Wildcard

=COUNTIF(A5:A9,”*~*apples*”) give a result of 1

The tilde wild card allows you to search for words that contain a wild card – either * or ?.

Simply placing the tilde before the asterisk tells Excel that the asterisk is not to be used as a wildcard.

There are other formulas you can use Wildcards with so feel free to experiment. Please post your discoveries in the comments below for all to share.

Download the workbook and have a play around.

If you liked this sign up for our Excel newsletter below and receive weekly tips & tricks like this straight to your inbox.

Tagged as: vlookup