How to treat wildcards as normal characters in the COUNTIFS function.(Microsoft Excel)

Japanese version.

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

---

Links

Tips. Others.(Microsoft Excel)