Explanation
In this example the goal is to sum the numbers in the range F5:F16 when corresponding cells in the range C5:C15 are not equal to “Red”. To solve this problem, you can use either the SUMIFS function or the SUMIF function . The SUMIF function is an older function that supports only one criteria. SUMIFS on the other hand can be configured to apply multiple criteria. Both options are explained below.
SUMIFS solution
In the example shown, the solution is based on the SUMIFS function. The formula in cell I5 is:
=SUMIFS(F5:F16,C5:C16,"<>red") // sum if not "red"
In this formula, sum_range is F5:F16, criteria_range1 is C5:C16, and criteria1 is “<>red”. The result ($136) is the sum of numbers in the range F5:F16 when corresponding cells in C5:C15 are not “Red”. Note that the SUMIFS function is not case-sensitive. With a criteria of “<>red”, SUMIFS will exclude “RED”, “Red”, and “red”. Also note that in the SUMIFS function, sum_range always comes first . To sum numbers when the state is not “TX” , you can adapt the formula like this:
=SUMIFS(F5:F16,D5:D16,"<>tx") // sum if not "tx"
In this formula, sum_range is F5:F16 as before, criteria_range1 is D5:D16, and criteria1 is “<>tx”. For more information about using SUMIFS to apply multiple criteria with logical operators (>,<,<>,=) and wildcards (*,?), see this page .
SUMIF solution
The SUMIF function is an older function in Excel that supports only a single condition. To solve this problem with the SUMIF function, you can use a formula like this:
=SUMIF(C5:C16,"<>red",F5:F16)
In this formula, range is D5:D16, criteria is “<>tx”, and sum_range is F5:F16. Note that in the SUMIF function, sum_range always comes last . The result ($136) is the same as with the SUMIFS function above. For more information about using SUMIF to apply criteria with logical operators (>,<,<>,=) and wildcards (*,?), see this page .
Notes
- For a case-sensitive solution, see this formula .
- To match a substring in a cell, see this formula .
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”.
| Target | Criteria |
|---|---|
| Cells with 3 characters | “???” |
| Cells equal to “xyz”, “xaz”, “xbz”, etc | “x?z” |
| Cells that begin with “xyz” | “xyz*” |
| Cells that end with “xyz” | “*xyz” |
| Cells that contain “xyz” | “xyz” |
| Cells that contain text in A1 | “"&A1&”" |
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))
