Using wildcard characters in Excel functions

Handling missing characters

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.

=SUMIF(A:A,C1,B:B)

Wildcard Example

If you had ABC in cell C1 you could insert the * into the formula as follows. See image below.

=SUMIF(A:A,C1&"*",B:B)

Wildcards Example

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.

Wildcard example

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.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *