SUM with Wildcards
I frequently use the SUMIF and SUMIFS functions. Is there a way to sum based on a part of a code? The codes maybe ABC123, ABC999 or BBB111 but I want to add up all the codes starting with ABC.
By Neale Blackwood
Both these functions can perform sums based on partial cell contents. Let’s assume that column A contains a code and column B contains values. The codes in column A have a three digit alpha code followed by a three digit numeric eg ABC123.
We can use the wildcards characters that Excel recognizes. The * character represents multiple characters. The ? character represents a single character. To sum all the codes starting with ABC you would use ABC* as the criteria. 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.
In our 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.
If you had ABC in cell C1 you could insert the * into the formula as follows.
These techniques are useful when there is a coding structure 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: SUMIFS and COUNTIFS are new functions and only available in Excel 2007.