# Count of numbers in a range equal to or less than criteria not including next lower number

1. ## Count of numbers in a range equal to or less than criteria not including next lower number

Criteria equals 40
Count numbers in range 40.0 to 39.1 but don't include 39.0 or any numbers higher than 40.0
See example  Register To Reply

2. ## Re: Count of numbers in a range equal to or less than criteria not including next lower nu

Maybe try

=COUNTIFS(A\$2:A\$9,"<="&D2,A\$2:A\$9,">"&D3)

or
=INDEX(FREQUENCY(A\$2:A\$9,D\$2:D\$5),ROWS(G\$2:G2))  Register To Reply

3. ## Re: Count of numbers in a range equal to or less than criteria not including next lower nu

The formula in f5 is having a problem because it references d6 which has no criteria.
The formula in g5 counts anything 37 or below

1) The criteria will be a finite list.

2) I don't want anything outside the criteria, if first criteria is 40 and last is 37 then nothing above 40 will be counted and nothing below 36.1 will be counted.

3) The criteria is always whole numbers and the data will be one decimal, the example included is exactly how the actual spreadsheet will be.  Register To Reply

4. ## Re: Count of numbers in a range equal to or less than criteria not including next lower nu

Maybe the following in E2 copied down ??
Formula:  `Please Login or Register  to view this content.`  Register To Reply

5. ## Re: Count of numbers in a range equal to or less than criteria not including next lower nu

Beautiful.
Thanks Geoff.  Register To Reply

6. ## Re: Count of numbers in a range equal to or less than criteria not including next lower nu

Most credit to Bo_Ry - just a minor tweak to Bo's formula. Thanks for the rep!  Register To Reply