Wildcard problem in Excel 2007

[kennethlim]

Hi, I'm a beginner in Excel and I'm trying out some wildcard usage in Excel but I have run into some problems. Hope you guys can help me with it. Thanks!

Okay, the problem is that I have the following info in the cells A1:A4:
434
454
31
122

Now in the next cell A5, I type the function =countif(A1:A4,"4?4"). But I get a number 0 rather than 2.

However when I change the cells A1:A4 to:
A3A
A5A
31
122

and i type =countif(A1:A4,"A?A"), the function worked and give me a result of 2.

How can I rectify this problem? Why does it work for letters and not numbers?

[DonkeyOte]

If the A1:A4 are formatted as Text prior to data entry then it will work.

The use of wildcard in COUNTIF means it looks for text strings... it follows that if the values are numbers (not text) the result will be 0.

[daddylonglegs]

Wildcards won't work with numeric data [as Donkeyote says]. A1:A4 has to be text formatted for your COUNTIF formula to work, otherwise, if data is all numeric you could use this formula

=SUMPRODUCT((INT(A1:A4/100)=4)*(MOD(A1:A4,10)=4))

or for text and/or numeric values in the range

=SUMPRODUCT(ISNUMBER(SEARCH("4?4",A1:A4))*(LEN(A1:A4)=3))

[kennethlim]

Oh, alright. That helped a lot. Thanks.