Excel: Inconsistent results of CountIf() on string with tilde

Question

Is this a bug in Excel, or is there a reason for the different results in the table below? If it's a bug, is there a way to predict what strings will be affected by it?

In both match() and CountIf(), I double a tilde to allow the searches to work properly. (This works because tilde is the wildcard escape character.) But I've come across two cases where this doesn't work for CountIf().

In the table below, columns E and F show that substitution of a double tilde allows match() to correctly find the string in the list. But columns G through J show that the same substitution in CountIf() works on rows 4 and 5 and does not work on rows 6 and 7. Instead, on rows 6 and 7, CountIf() works without the substitution and does not work with the substitution.

	B	C	D	E	F	G	H	I	J
	String	Formula	Result	Formula	Result	Formula	Result	Formula	Result
4	{FK*BD6Qc)9j~aHa	=MATCH($B4, $B:$B, 0)	#N/A!	=MATCH(SUBSTITUTE($B4, "~", "~~"), $B:$B, 0)	4	=COUNTIFS($B:$B, $B4)	0	=COUNTIFS($B:$B, SUBSTITUTE($B4, "~", "~~"))	1
5	;.D~n[sg9#?$}z3`	=MATCH($B5, $B:$B, 0)	#N/A!	=MATCH(SUBSTITUTE($B5, "~", "~~"), $B:$B, 0)	5	=COUNTIFS($B:$B, $B5)	0	=COUNTIFS($B:$B, SUBSTITUTE($B5, "~", "~~"))	1
6	JFxa7V9."Ap~/Q2g	=MATCH($B6, $B:$B, 0)	#N/A!	=MATCH(SUBSTITUTE($B6, "~", "~~"), $B:$B, 0)	6	=COUNTIFS($B:$B, $B6)	1	=COUNTIFS($B:$B, SUBSTITUTE($B6, "~", "~~"))	0
7	dP4%5>by{Bw#Vt~D	=MATCH($B7, $B:$B, 0)	#N/A!	=MATCH(SUBSTITUTE($B7, "~", "~~"), $B:$B, 0)	7	=COUNTIFS($B:$B, $B7)	1	=COUNTIFS($B:$B, SUBSTITUTE($B7, "~", "~~"))	0

What's going on here? What's the right way to account for the possibility of there being a tilde in the criteria range of CountIf()?

Answer 1

For reasons which are unclear to me, in defining the criteria argument for COUNTIF, the tilde ~ must be escaped if and only if there is an actual wild-card character {asterisk, question-mark} present.

I suppose in order to use COUNTIF one could use an IF statement to decide whether to create the escapes or not. Of course, one should escape ALL of the wild-card characters.

=COUNTIF(
    B:B,
    IF(
        OR(ISNUMBER(FIND({"*", "?"}, B4))),
        REDUCE(
            B4,
            {"~", "*", "?"},
            LAMBDA(a, b, SUBSTITUTE(a, b, "~" & b))
        ),
        B4
    )
)

seems to work on all examples.

Update
The common denominator seems to be that with COUNTIF, a tilde is recognized as an escape character if it is followed by a wild-card character, even if there are intervening non-wild card characters. With MATCH, it is always treated as an escape character.

A "Microsoft Agent" has responded to a post of mine on the Microsoft Answers Community with a comment that this behavior is "by design". To my way of thinking, this design is illogical and non-intuitive. I have submitted Feedback via the Help menu in Excel reporting it and asking that the behavior either be changed or documented.

Comments or additional Feedback submissions might do some good.

Answer 2

The issue with the search is that the data you have contains wildcards and the functions you are using like COUNTIF and MATCH accept the use of wildcards. You can find the list of functions that support wildcards here.

What you need to do is use a function that does not support wildcards like SUMPRODUCT, the downside is that in only works with a limited range instead of the full column.

Here is an example of how to use the formula.

=SUMPRODUCT(--($B$4:$B$7=B4))

Here is an explanation:

$B$4:$B$7=B4 this will match the range with the column and mark the matches as TRUE OR FALSE

--() This will take the matches and turn them into numbers. 1 for TRUE and 0 for FALSE

SUMPRODUCT will take the entire response for the previous match and sum the values.

	B	C	D
	String	Formula	Result
4	{FK*BD6Qc)9j~aHa	=SUMPRODUCT(--($B$4:$B$7=B4))	1
5	;.D~n[sg9#?$}z3`	=SUMPRODUCT(--($B$4:$B$7=B5))	1
6	JFxa7V9."Ap~/Q2g	=SUMPRODUCT(--($B$4:$B$7=B6))	1
7	dP4%5>by{Bw#Vt~D	=SUMPRODUCT(--($B$4:$B$7=B7))	1

This way you don't need to replace the tildes in the string and still get the matches.

---

Edit, for the issue with rows 6 and 7:

Rows 6 and 7 do not have other wildcard values like * or ?, so when the string is evaluated, it takes the string values as is.

So this value JFxa7V9."Ap~~/Q2g gets evaluated as that, a string with a double tilde, so it does not match JFxa7V9."Ap~/Q2g which only has a single tilde.

You can test adding a wildcard to the values and see that the matches change:

JFxa7V9."Ap~/Q2g -> 0
JFxa7V9.?"Ap~/Q2g -> 1
JFxa7V9.*"Ap~/Q2g -> 1

dP4%5>by{Bw#Vt~D -> 0
dP4%5>by?{Bw#Vt~D -> 1
dP4%5>by*{Bw#Vt~D -> 1