+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    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
    Attached Files Attached Files
    Last edited by skywriter; 10-01-2020 at 03:28 PM.
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    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))
    Attached Files Attached Files

  3. #3
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    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.

  4. #4
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    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: copy to clipboard
    Please Login or Register  to view this content.
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select ?Solved? from the Thread Tools menu

  5. #5
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

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

    Beautiful.
    Thanks Geoff.

  6. #6
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. count equal numbers on two columns for given number
    By pedersenn in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-15-2016, 01:59 PM
  2. [SOLVED] Count and sum with multiple criteria including date range
    By Perk1961 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-23-2016, 04:46 PM
  3. [SOLVED] Need a Formula to count the number of different values in a range not equal to a fixed#
    By JohnnyEaring in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-12-2016, 11:07 AM
  4. [SOLVED] Count based on 3 criteria including value range
    By Perk1961 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-13-2015, 10:43 PM
  5. [SOLVED] Count cells in a range, starting from first cell with number that does not equal zero
    By ldoherty in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-27-2014, 10:06 AM
  6. [SOLVED] Round number to nearest equal or lower number of a string
    By MikeMar! in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-20-2013, 01:26 PM
  7. [SOLVED] Count based on 3 criteria including date range
    By timarcarze in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 06-13-2013, 01:43 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1