4

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
  • The test condition in Excel's COUNTIF is not a regular expression, or similar. It takes the form of "[conditional operator][value]". Alphabetical [value]'s (for English, at least) are tested by default in case-insensitive alphabetical order. – hBy2Py Dec 31 '14 at 19:11
5

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.