Using wildcard characters allows you to create flexible calculations. When used with the SUMIF and SUMIFS functions you can include quite complex criteria.
The wildcard characters are * and ?.
The * character represents multiple characters. The ? character represents a single character. To sum all the codes starting with ABC in a SUMIF function you would use ABC* as the criteria.
As an example if cell C1 contains ABC* then the following formula will add up all the values in column B with a code starting with ABC in column A. See image below.
If you had ABC in cell C1 you could insert the * into the formula as follows. See image below.
The & symbol joins text together.
If you wanted to sum all the codes that contained a C the criteria would be *C*.
If you wanted to sum the codes that had C as the second character you would use ?C* as the criteria. See image below.
These techniques are useful when there is a structured coding system in place and the placement of characters means different things.
The wildcard characters can also be used with other functions including COUNTIF, COUNTIFS, VLOOKUP and MATCH.
Note: both SUMIFS and COUNTIFS are only available in Excel 2007 and later versions.