COUNTIF with Decimal Numbers Acting as Whole Numbers

I'm trying to construct a COUNTIF formula which counts the number of 3s (rounded) in a given range. The problem is, these numbers are decimal and therefore are not being read. Even when I use the ROUND function I cannot get it to count them.

This is the formula I'm currently using:

Formula:

Please Login or Register  to view this content.

And this is the range of data I am counting.

3.00000032436
0.00000032436
3.00000091496
3.00000032436
3.00000038436
0.00000024269

use a countifs

COUNTIFS(A1:F1;">=3";A1:F1;"<4")

or

COUNTIFS(A1:F1;">=3";A1:F1;"<3.005")

in what ever precision you want.

Originally Posted by Roel Jongman

use a countifs

COUNTIFS(A1:F1;">=3";A1:F1;"<4")

or

COUNTIFS(A1:F1;">=3";A1:F1;"<3.005")

in what ever precision you want.

I know I said I was counting 3s, but really I need an adaptable formula that can cater for different numbers as well. I need it to round up or down the numbers in a range to whole numbers. 0.00000000001 would be 0, 3.49999999999 would be 3, 1.83679146257 would be 2 etc.

you do not need to round for that you can just incorporate in formula, normal rounding rules are not difficult to calculate upper and lower limit without rounding formula.

Lets assume that the value you look for is in cel A3

then the formula is

Formula:

Please Login or Register  to view this content.

if in A3 you put wholenumeber 3
Than any value between 2,5 and 3,499999.... will count for the countif formula above

You can make it more flexible by have 2 input cells for upper and lower limit. Then you can control exact range of numbers to find

If you need something different then please upload en excel example file it is then a lot easier to see what you mean and to build working formula.