Sum If Cells Contain An Asterisk

Explanation

The goal in this example is to sum Prices in column C when the Items in column B contain an asterisk (). The challenge is that the asterisk () is reserved as a wildcard in functions like the SUMIFS function, so you can’t match a literal occurrence of this character without using a special syntax.

Wildcards

Excel functions like SUMIF and SUMIFS support the wildcard characters “?” (any one character) and “” (zero or more characters), which can be used in criteria. Wildcards allow you to create criteria to target cells that “begin with”, “end with”, “contain 3 characters” and so on. The table below shows some examples. For this problem, we want to use the “Cells that contain text in xyz” pattern, which uses two asterisks (), one before and one after the search string like this “xyz”.

Because asterisks and question marks are themselves wildcards, if you want to search for these characters specifically, you’ll need to escape them with a tilde (~). The tilde causes Excel to handle the next character literally .

SUMIFS solution

To sum Prices in column C when the Items in column B contain an asterisk (*), the formula in cell F5 is:

=SUMIFS(C5:C16,B5:B16,"*~**")

In this case we are using “~” to match a literal asterisk, but this is surrounded by asterisks on either side, in order to match an asterisk anywhere in the cell . If you just want to match an asterisk at the end of a cell, you can use “~” for the criteria. To sum Prices for Item names that do not contain an asterisk (""), the formula in cell F6 is:

=SUMIFS(C5:C16,B5:B16,"<>*~**")

This formula simply prepends the not equal to operator ("<>") to the existing criteria. For more details about using other operators in the SUMIFS function, see this page .

SUMIF solution

This problem can also be solved with the older SUMIF function , which only supports a single condition. The equivalent formulas based on SUMIF look like this:

=SUMIF(B5:B16,"*~**",C5:C16) // contains *
=SUMIF(B5:B16,"<>*~**",C5:C16) // does not contain *

Other options

In Excel, there is always another way to skin the cat. If you don’t like the fiddly syntax needed to escape wildcards above, you can use a formula based on the FIND function to search for an asterisk directly. One option that will work in any version of Excel is SUMPRODUCT + FIND:

=SUMPRODUCT(ISNUMBER(FIND("*",B5:B16))*C5:C16)

This works because, unlike the SEARCH function , the FIND function does not support wildcards. Another option in newer versions of Excel is to use the SUM function with the FILTER function :

=SUM(FILTER(C5:C16,ISNUMBER(FIND("*",B5:B16)),0))

Explanation

In this example, the goal is to sum the numbers in column C when the text in column B contains specific pairs of colors. For example, the formula should sum a number when the text contains both “red” and “blue”. Order is not important; the two colors can appear anywhere in the cell. However, both colors must appear in the same cell. This problem can be solved with the SUMIFS function , which is designed to sum numbers based on multiple criteria.

SUMIFS function

The SUMIFS function sums cells in a range that meet one or more conditions, referred to as criteria . The syntax for the SUMIFS function depends on the number of conditions needed. Each separate condition will require a range and criteria . The generic syntax for SUMIFS looks like this:

=SUMIFS(sum_range,range1,criteria1) // 1 condition
=SUMIFS(sum_range,range1,criteria1,range2,criteria2) // 2 conditions

In this case, we need two conditions, one to test for “red”, and one to test for “blue”. This means both criteria will be applied to the same range, the text in B5:B16. We start with the sum_range , which is the numbers in the range C5:C16:

=SUMIFS(C5:C16,

Then we add the first range/criteria pair to test for “red”:

=SUMIFS(C5:C16,B5:B16,"*red*",

Note that we surround “red” with an asterisk () on either side. The asterisk () is a wildcard available in the SUMIFS function, which means “zero or more characters”. We use a wildcard in this case to match “red” occurring anywhere in the text. Next, we add a second range/criteria pair to test for “blue” to complete the formula:

=SUMIFS(C5:C16,B5:B16,"*red*",B5:B16,"*blue*")

Again, we use two asterisks (*) as wildcards to match “blue” in any location. Notice we are applying two different criteria to the same range , B5:B15. This is intentional. The SUMIFS function applies criteria based on AND logic, which means that both conditions must be true for SUMIFS to include a value in the final result. In other words, both “red” and “blue” must exist in the text. Note that SUMIFS is not case-sensitive. Using “red” for criteria will match “Red”, “RED”, and “red” in any location.

Other combinations

The other color combinations in the worksheet shown use the same pattern. To test for “pink” and “purple”, and “green” and “blue”, the formulas in F6 and F7 are as follows:

=SUMIFS(C5:C16,B5:B16,"*pink*",B5:B16,"*purple*")
=SUMIFS(C5:C16,B5:B16,"*green*",B5:B16,"*blue*")