Why does COUNTIF think "?" is in the criteria when its actually not.

[marlina]

i am trying to count total numbers in this:

12/11/1991?

with this formula:

=SUMPRODUCT(COUNTIF(Isdigt3,MID(C9,ROW(1:50),1)))

where Isdigt3 is range containing 0,1,2,3,4,5,6,7,8,9,/,

it should return 10. but it returns 11.

I don't have "?" in my range

 

[RoryA]

? is a wildcard meaning any single character. To escape it you need to use ~?, so use:

=SUMPRODUCT(COUNTIF(Isdigt3,SUBSTITUTE(MID(C9,ROW(1:50),1),"?","~?")))

 

[marlina]

? is a wildcard meaning any single character. To escape it you need to use ~?, so use:

=SUMPRODUCT(COUNTIF(Isdigt3,SUBSTITUTE(MID(C9,ROW(1:50),1),"?","~?")))

Thank you

 

[markmzz]

Hi!

You can try the Array Formula (use Ctrl+Shift+Enter to enter the formula) below too:

=COUNT(SEARCH(Isdigt3,MID(C9,ROW(1:50),1)))

Or the Normal Formula below

=COUNT(INDEX(SEARCH(Isdigt3,MID(C9,ROW(1:50),1)),))

Markmzz