As per below I cannot use a wildcard in an Excel query to do an 'ends with' count. I believe everything is correct so any ideas welcome

=COUNTIF('RANGE'!$A$2:$A$5,"1" & A1) - works and shows a count of 1 =COUNTIF('RANGE'!$A$2:$A$5,"*" & A1) - fails and shows a count of 0 instead of the expected 2

CodePudding user response：

You can use SUMPRODUCT instead:

=SUMPRODUCT(--(RIGHT(B1:B6,LEN(A1))=A1&""))

CodePudding user response：

Wildcards can only be used on strings

CodePudding user response：

In answer to the question
Oddly enough, once you use wildcards in COUNTIF, it's looking for textual values.
So, what you're trying to do would work with numbers stored as text, but not numbers per se.

See COUNTIF Help.
There you will see an explicit example where "*" is used to count any cells containing Text.
Indeed, if you test =COUNTIF(RANGE!$A$2:$A$5,"*") on your data you will see it only returns non-zero results for cells containing Text (including numbers stored as text), but not numbers as such.

Other ways to approach this:
a) You could use an array formula based on SUM:

{=SUM(IF(RIGHT($A$2:$A$5,LEN(A1))=TEXT(A1,0),1))}

b) You could use array formula based in COUNT:

{=COUNT(IF(RIGHT($A$2:$A$5,LEN(A1))=TEXT(A1,0),$A$2:$A$5))}

c) You could use SUMPRODUCT as suggested by @basic:

=SUMPRODUCT(--(RIGHT($A$2:$A$5,LEN(A1))=A1&""))

d) Etc... (I'm sure there are others)

Of course, none of these address the question as asked. Still, one or the other might be helpful.