How to treat wildcards as normal characters in the COUNTIFS function.(Microsoft Excel)
Some strings are treated as special characters called wildcards in the tabulation function with IF (with conditional judgment) and cannot be tabulated as intended.
This becomes a problem with the COUNTIF and COUNTIFS functions, which search for strings.
This article shows how to count those characters as normal characters.

Steps
The following are not treated as characters.
- * any number of strings.
- ? any single character.
If the ~ is placed in front of the * or ? are treated as normal characters, not wildcards.
Use the SUBSTITUTE function to replace them.
=COUNTIFS(criteria_range,SUBSTITUTE(SUBSTITUTE(criteria,"","~"),"?","~?"))
=COUNTIFS(B3:B6,SUBSTITUTE(SUBSTITUTE(B2,"","~"),"?","~?"))

---
Discussion
New Comments
No comments yet. Be the first one!