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,"","~"),"?","~?"))
---