countif function/formula not working!!!

Question

Tearing my hair out here!!  Trying to count the occurrences of a number (coded 1) in a huge data dump.   Am using the formula

=countif(range of cells,"1")   I've tried removing the " " around the 1 - no difference.   Anyone help - I'm clearly doing something stupid!

thanks

I've just highlighted the range of cells in the above formula?????

Answer 1

If Excel complains that there is a problem with the formula, try Tom Ogilvy's suggestion of using a semi-colon:

=COUNTIF(D5:D9;"1")

or

=COUNTIF(D5:D9;1)

Answer 2

Just for grins, try this

=COUNTIF(E:E,"*1*")

that seems to work with spaces and the non-breaking space character (Char(160))  which can be present if you copied your data from the web. 

It won't count a number like  1, 123 or 2114  if they are stored as numbers.

However you statement that there is a problem with the formula would not be raised because of the data.  It generally implies that you have improperly formatted the range argument or are using something like a semi-colon (;) for list separator when you actually need a comma (,)  (the first argument can only be a reference to a single contiguous range)

--

Regards,

Tom Ogilvy

Answer 3

Could there be spaces before or after the 1s in the range?

Answer 4

=countif(D5:D9,"1")                  i.e is to identify the no of 1's in a particular range................loads more ranges to cover tks!

Feeling more stupid by the minute

Answer 5

thanks but I've double checked that!  Infuriating. Error message states that "theres a problem with this formula"