This formula sums the Price in column C when the Item name in column B contains an asterisk ("*").
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: 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: This formula simply prepends the not equal to operator ("<>") to the existing criteria. For a 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:
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: 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: Note that both of these functions use the same logic to locate text that contains an asterisk: ISNUMBER + FIND. To read more about how this part of the formula works, see this example.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
