in the following example you get output '0' for a strange reason. Can someone explain why this occurs and what I can do about it? (The format of the cells is "text")
rows A:A
---------
1
11
111
22
2
1111
formula:
--------
=COUNTIF(A$1:A$65536;"1*")
result:
-------
0
wanted result:
--------------
4
I think your values are numbers. Test by using
=ISNUMBER(A1)
If you apply text format after entry it doesn't change the format to text - you either have to apply text format before entry or enter the values with a preceding apostrophe (to indicate text) like
'111
COUNTIF function with wildcards doesn't work with numbers. You can use this formula to get the required result (which will work with text, numbers or a mix)
=SUMPRODUCT(ISNUMBER(SEARCH(1;A$1:A$65536))+0)
