+ Reply to Thread
Results 1 to 5 of 5

CountIf and date range

  1. #1
    Registered User
    Join Date
    01-18-2013
    Location
    Funtown, USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    CountIf and date range

    I have an interesting situation that i'm trying to write a formula for. The spreadsheet I've attached is a sample of a more expansive production. I compile this expansive report 3 times a week and send it out to the folks who have assignments to show each item they're assigned and if the deadline is coming due (based on a R/Y/G grading system). The report provides a summary sheet, as well as sheets with the detailed items based on which of 3 statuses their assignments are in - Review, Investigate, or Action Pending.

    My problem lies when compiling the formulas for the summary sheet, trying to summarize the Review status. I've got Investigate and Action Pending solved, but the Review is a bit more tricky.

    Items in Review should be reviewed and moved to one of the other statuses within 2 days of the assigned date (technically, 2 business days, but that's less important for my question). I can throw in a Countif for all the items in the Assigned Date column (column E) that are less than TODAY and it works fine, however, I need to count the dates in column E +2 days and compare against Today.

    TLDR:
    I need to add 2 days to all the dates in column E of the Review sheet and count how many of those dates are less than TODAY (and place that count on C2 of the Summary sheet)
    essentially, something along the lines of (in C2 of the Summary sheet) =COUNTIF(Review!E:E+2,"<"&TODAY()), even though I know that formula won't work

    I was able to get the conditional formatting to work this out to apply R/Y/G as needed, and I'm fully aware I could just throw in an additional column to add the 2 days, and base my count off of the additional column, but I'm trying to keep everything clean for the end user. So, if there's a way to add 2 days to a date range within a counting formula, I'd love to find it.

    Thanks, as always!
    Attached Files Attached Files
    Last edited by ggentry; 06-12-2018 at 12:13 PM.

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: CountIf and date range

    Try reversing your logic

    =COUNTIF(Review!E:E,"<"&TODAY()-2)

  3. #3
    Registered User
    Join Date
    01-18-2013
    Location
    Funtown, USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: CountIf and date range

    Quote Originally Posted by jason.b75 View Post
    Try reversing your logic

    =COUNTIF(Review!E:E,"<"&TODAY()-2)
    Jason.b75

    Thank you! That worked. I thought about trying it several times but kept dismissing the idea. Occam's bleeping razor.
    Thanks again.

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: CountIf and date range

    Quote Originally Posted by ggentry View Post
    I thought about trying it several times but kept dismissing the idea.
    When the mountain did not come to Muhammad, did Muhammad dismiss the idea that he must go to the mountain?

  5. #5
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: CountIf and date range

    for 2 business days from Assigned Date
    =SUMPRODUCT(--(WORKDAY(_t[Assigned Date:]+0,2)<$H$1))
    Attached Files Attached Files

+ 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. COUNTIF used with a DATE range
    By bobbles22 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-14-2014, 04:38 AM
  2. [SOLVED] COUNTIF with date range
    By erincox2010 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-11-2014, 06:40 PM
  3. Countif Date Range
    By LisaG in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-30-2012, 06:19 AM
  4. COUNTIF date will expire in a date range
    By neilanderson in forum Excel General
    Replies: 6
    Last Post: 07-31-2012, 02:08 PM
  5. [SOLVED] countif using date range
    By dev111ski in forum Excel General
    Replies: 10
    Last Post: 05-23-2012, 05:44 AM
  6. Countif with date range
    By webisti in forum Excel General
    Replies: 4
    Last Post: 09-29-2011, 10:49 AM
  7. Combining a Date Range COUNTIF and a general COUNTIF
    By jacobtom in forum Excel General
    Replies: 1
    Last Post: 09-15-2011, 05:06 PM

Tags for this Thread

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