Math
SUMIF & COUNTIF when cells contain a literal asterisk (*) or question mark (?)
Escape wildcard characters with a tilde ("*~**") so SUMIF and COUNTIF match literal asterisks, question marks, or tildes instead of every text row.
Why =SUMIF(A2:A5, "***", B2:B5) silently sums EVERY text row—and how ~ fixes it!
SUMIF and COUNTIF criteria interpret * as a sequence wildcard and ? as a single-character wildcard. In this all-text SKU example, criteria *** matches all four labels and yields 550. That is valid wildcard behavior, but it does not implement a search for a literal asterisk.
Prefixing any wildcard character with a tilde (~) escapes it into a literal character: ~* matches a literal asterisk, ~? matches a literal question mark, and ~~ matches a literal tilde! Wrapping ~* between outer wildcards—"*~**"—means "any text, followed by a LITERAL asterisk, followed by any text"!
| SKU Code (A) | Units (B) | Broken "***" Match? | Escaped "*~**" Match? |
|---|---|---|---|
| PRO*2026 | 150 | Matched (Wildcard!) | Matched (Has literal *) |
| PRO-2026 | 200 | Matched (Bug!) | Ignored (Hyphen, no *) |
| STD*2026 | 90 | Matched (Wildcard!) | Matched (Has literal *) |
| STD-2026 | 110 | Matched (Bug! -> Total 550) | Ignored (-> True Total 240!) |
Sum rows containing a literal asterisk using "*~**"
Outer * wildcards match surrounding characters while the middle ~* matches a literal *.
=SUMIF(A2:A5, "*~**", B2:B5)Count cells containing a literal asterisk
Use "*~?*" to count literal question marks or "*~~*" to count literal tildes.
=COUNTIF(A2:A5, "*~**")Try the example
Use the original values or change them to test your understanding.