What is a wild card?
With some functions in Excel like SUMIF(), COUNTIF(), AVERAGEIF(), etc, you need to specify a criteria to filter the result.
For example, if you have a list of daily sales by products, and wanted a total of the sales for the month of a particular product, you would use SUMIF() with the name of the product as a criteria.
SUMIF(B2:G21, "Acme pen", G2:G21)This would match the criteria “Acme pen” exactly and provide the sum of all rows matching that criteria.
However, if you had several products which share almost similar names, like “Acme pen”, “Ajax pen”, etc, and wanted a total for all pen products, then you can use a wild card in the criteria.
What are wild cards?
There are 2 types of wild cards that you can use in Excel functions:
The asterisk symbol – *
Use the asterisk symbol to match any number of characters before or after the criteria you specify.
For example, using the 2 products mentioned above, if you wanted to sum up sales for all Acme products then your formula will look like this:
SUMIF(B2:G21, "Acme*", G2:G21)The asterisk at the end of Acme will tell Excel that as long as the product name starts with Acme, sum the value.
This would then match “Acme pen”, “Acme eraser”, etc.
If you wanted to match the starting characters, then put the asterisk before the criteria.
SUMIF(B2:G21, "*pen", G2:G21)This would match “Acme pen” as well as “Acme pencil”, as well as “Ajax pen“, but not “Acme eraser”.
You can use the asterisk wild card before and also after a criteria that you specify.
SUMIF(B2:G21, "*me*", G2:G21)The question mark – ?
Where the asterisk wild card will match any number of characters before or after the criteria, the question mark wild card matches a single character.
SUMIF(B2:G21, "Acme P?n", G2:G21)This will match “Acme Pen” and also “Acme Pan”.
You can use the question mark wild card before or after a criteria or both.
Any limitation to the number of wild cards?
For both wild cards, you can use them multiple times in specifying your criteria, as well as use a combination of both.
For example:
SUMIF(B2:G21, "A*e*", G2:G21)This will match all values that start with “A” and have “e” in it as well.
However, the formula can get fairly complex and may not have the desired result, if you use too many wild cards.
Are wild cards case sensitive?
In most cases, no.
So you can have “Acme*” or “acme*” and it will have the same result.
There are some functions that are case sensitive, so it’s best to test first to see if it needs to be in the proper case to find a match.
Did you know?
Wild cards are not specific to Excel and can be used in many other software applications.
In Windows, one way you can use wild cards is when you are searching for a file.
Did you find this article helpful? Please rate and also share your thoughts in the comments section below.