Math & Trig
Count cells containing specific text: substrings and literal ? or * wildcards
Wrap search terms in "*" & B2 & "*" inside COUNTIF for partial matches, and escape literal question marks or asterisks with ~.
Substring wildcards vs literal punctuation (~? and ~*)
In COUNTIF criteria, * matches zero or more characters and ? matches any single character. Surrounding a keyword cell with "*" & B2 & "*" counts every cell containing that substring case-insensitively.
Because ? and * are active wildcards, searching "*?*" matches every cell with at least 1 character! Prefixing a tilde (~? or ~*) tells COUNTIF to match an actual question mark or asterisk character.
The dynamic keyword formula treats *, ? and ~ in B2 as pattern syntax. Escape them before constructing the criterion when the keyword should be literal; reject an empty keyword if matching all text is not intended.
| TicketTitle (A) | Matches "*login*" | Matches "*~?*" | Matches "*~**" |
|---|---|---|---|
| SSO login timeout | Yes | No | No |
| Reset password? urgent | No | Yes | No |
| Mobile LOGIN crash | Yes | No | No |
| Billing *promo* code | No | No | Yes |
Dynamic substring count and escaped wildcard checks
With Keyword = "login" in B2, "*" & B2 & "*" matches both "SSO login timeout" and "Mobile LOGIN crash" (2 cells).
=COUNTIF(A2:A5, "*" & B2 & "*")Count literal question marks with ~?
"*~?*" matches only "Reset password? urgent" (1), whereas unescaped "*?*" matches all 4 non-empty text cells.
=COUNTIF(A2:A5, "*~?*")Try the example
Use the original values or change them to test your understanding.