+ Reply to Thread
Results 1 to 5 of 5

In a range of days, count how many times one value falls between two other values

  1. #1
    Registered User
    Join Date
    01-06-2013
    Location
    Sacramento,CA
    MS-Off Ver
    2013
    Posts
    36

    In a range of days, count how many times one value falls between two other values

    I need a formula to keep a running count of how many times over a range of days, that a daily calculated value falls between the high and low values of that same day.
    Countif looks like the onw to use, but if there is a more suitable function , I'm all ears. Thanks in advance, all.
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: In a range of days, count how many times one value falls between two other values

    Hi,

    I might be more flexible to use a helper row and in say B8 copied across enter

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Otherwise an array formula like

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    01-06-2013
    Location
    Sacramento,CA
    MS-Off Ver
    2013
    Posts
    36

    Re: In a range of days, count how many times one value falls between two other values

    Thanks, Richard. Regarding the first possibility, I have done that before and it does work. My issue here is that I have almost 200 rows of calculated data like R1 that I would need to do that for, so I'm looking for a shortcut of sorts.
    As for the alternate using SUMPRODUCT, the formula does work, but it also counts the days that have no data.
    It give3s mne a ount of 9, wher it should only return the number 5

    I need a combo with an if statement : i.e. if B5:K5 = 0.00, don't count it

    I just don't know how to combine an if statement with SUMPRODUCT. any ideas?

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: In a range of days, count how many times one value falls between two other values

    Hi,

    Untested, but just add another element to the SUBPRODUCT

    =SUMPRODUCT((B5:J5>=B4:J4)*(B5:J5<=B3:J3)*(B5:K5<>0))

  5. #5
    Registered User
    Join Date
    01-06-2013
    Location
    Sacramento,CA
    MS-Off Ver
    2013
    Posts
    36

    Re: In a range of days, count how many times one value falls between two other values

    Richard,
    You saw exactly what I wanted, and that was a qualifier NOT to count blank cell in row 5.

    Thank you !! This one works exactly as I need it.

+ 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. Replies: 8
    Last Post: 07-14-2013, 07:29 PM
  2. Replies: 2
    Last Post: 01-10-2012, 04:06 PM
  3. Replies: 2
    Last Post: 10-29-2009, 04:52 PM
  4. [SOLVED] get a count of numbers whose value falls within a given range
    By LyleB_Austin in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM
  5. get a count of numbers whose value falls within a given range
    By LyleB_Austin in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 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