Wildcards are useful when you want to specify partial criteria such as “begins with”, “ends with” or “contains”. In this tutorial we will look at the correct way to use wildcard syntax in Excel’s COUNTIFS and SUMIFS function. Please download the example file to see the following in context.
Specifying Text Values Only
If your criteria is to search out text values only use “*”. Please note the use of the double inverted commas.
In the example file we used =COUNTIFS(C2:C25,”<“&E1, “*”A2:A25,”*”) to specify that we were only interested in text values in column A.
Specify Numeric Values Only
One way of specifying numeric values would be to search for values that are not text, using “<>*”
=COUNTIFS(C2:C25,”<“&E1,A2:A25,”<>*”)
“Starts with” Wildcard
To search for anything starting with a set of characters place an asterix after the characters such as “ABC*”. This would search for anything starting ABC. Please note the use of the double inverted commas.
=COUNTIFS(C2:C25,”<“&E1,A2:A25,”ABC*”)
“Ends with” Wildcard
To search for anything ending with a set of characters place an asterix before the characters such as “*123”. This would search for anything ending in 123. Please note the use of the double inverted commas.
=COUNTIFS(C2:C25,”<“&E1,A2:A25,”*123”)
“Contains” Wildcard
To search for anything containing a set of characters place an asterix before and after the characters such as “*CHEESE*”. This would search for anything containing CHEESE. Please note the use of the double inverted commas.
=COUNTIFS(C2:C25,”<“&E1,A2:A25,”*CHEESE*”)
? Wildcard
The ? wildcard allows you to specify any character in that position, for example “abc???124” will search for values that start with abc and end with 124 but have any characters in position 4, 5 and 6.
=COUNTIFS(C2:C25,”<“&E1,A2:A25,”abc???124”)
~ (tilde) Character
The tilde character is useful if you want to search for the wildcard characters ? and * as part of your criteria. For example “*~?*” would find any values that contain a question mark. You could also use the tilde character to specify a tilde in your criteria by using two tildes ~~
=COUNTIFS(C2:C25,”<“&E1,A2:A25,”*~?*”)