COUNTIF not counting

Question

Using the COUNTIF function either using freetext entering or formula builder results in a zero in all cells. Eg count number of cells in the range where the result is (say) 37,  I know the answer is not zero. The syntax, copied from the spreadsheet is: 

=COUNTIF($S$43:$S$68,37)

it doesn't matter if I don't use $'s or use "37" or "=37".  The value (37) in the range is derived from a formula. If I cut & 'paste values' of range COUNTIF still doesn't find them. If I type the value into the range it counts it!

Am I missing something?

Thanks

Paul

Answer 1

What you're missing is that the formula doesn't actually return exactly 37. COUNTIF(), like all XL formulae, looks at the stored value of the cell, not what is displayed. Depending on formatting, the two can be quite different.

Like nearly every spreadsheet extant, XL uses floating point double precision, which allows about 15 decimal digits of precision, so the vast majority of non-integer calculations will have the possibility of some small rounding error.

For instance, if you have formatting set to show zero places after the decimal point, any number from 36.5000000000000 to 37.4999999999999 will display as 37, but COUNTIF() will only count 37.000000000000000

Couple of suggestions:

• Round your data

=ROUND(<your current formula here>, 0)

• Look for a range, say plus or minus 0.01, inclusive:

=COUNTIF($S$43:$S$68,">="& 37 - 0.01) - COUNTIF($S$43:$S$68,">" & 37 + 0.01)

• IF you are VERY judicious in how you format your values, you may be able to set XL Preferences/Calculation to "Precision as displayed", which will automatically round/truncate values to whatever precision your formatting is set at. This is not a trivial exercise - small errors can balloon quickly.

Answer 2

Thanks J.E.

After I'd slept on it I came to same conclusion. Have used the ROUND function, although INT (integer) and ROUNDUP will also do the job, depending on what you want.

Your 2nd suggestion is also v useful for the future.