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.

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

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

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

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.