+ Reply to Thread
Results 1 to 6 of 6

COUNTIFS - Where Closed date is less than or equal to Open date +7

  1. #1
    Registered User
    Join Date
    09-12-2012
    Location
    Richmond, VA
    MS-Off Ver
    Varies but mostly 365
    Posts
    15

    COUNTIFS - Where Closed date is less than or equal to Open date +7

    Good morning all,

    I need a help with a COUNTIFS formula. I'm attempting to count cells where closed tickets have been closed within 7 days of the open date. I'm attempting to use the following formula:

    =COUNTIFS(Dataset[State],"Closed", Dataset[Closed],"<="&(Dataset[Opened]+7))

    This gives me a #SPILL! ... If I move the formula to a new sheet so I can see the spill it's giving:

    51
    52
    52
    53
    53
    54
    54
    54
    54

    If I break up the formula the Count/State/Closed portion works as expected. It's the 'date maths' between open and closed that is throwing the #spill. Also as you can see I'm working with date/time stamps but I figured countifs could handle it.

    Any help or suggestion is welcomed!
    Thanks,
    Jason.

    EDIT: I just thought of another route... Nested IF statement in the COUNTIFS... maybe I can use something like: IF(Dataset[Closed]-(Dataset[Opened]+7)<=7. Let me try that. brb

    Ok, these formula parts work, now to wrap them in an =SUM or 'sum' other way to concatenate the whole mess.
    =COUNTIFS(Dataset[State],"Closed")
    =IF(Dataset[Closed]-Dataset[Opened]<=7,"1","")

    OK, almost there.. maybe. But it's giving me all zeros...
    =COUNTIFS(Dataset[State],"Closed",Dataset[Closed],IF(Dataset[Closed]-Dataset[Opened]<=7,"1",""))
    Attached Files Attached Files
    Last edited by JasonMS; 12-05-2022 at 11:42 AM. Reason: Added attachment data and some additional work steps.

  2. #2
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    3,237

    Re: COUNTIFS - Where Closed date is less than or equal to Open date +7

    Please update the Excel version in your profile and attach a sample workbook.

    Read yellow banner at the top on how to attach a workbook.

  3. #3
    Registered User
    Join Date
    09-12-2012
    Location
    Richmond, VA
    MS-Off Ver
    Varies but mostly 365
    Posts
    15

    Re: COUNTIFS - Where Closed date is less than or equal to Open date +7

    Updated as requested. Thank you.

  4. #4
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    3,237

    Re: COUNTIFS - Where Closed date is less than or equal to Open date +7

    Try, =SUM(FILTER(IF(Dataset[Closed]-Dataset[Opened]<=7,1,0),Dataset[State]="Closed"))

  5. #5
    Registered User
    Join Date
    09-12-2012
    Location
    Richmond, VA
    MS-Off Ver
    Varies but mostly 365
    Posts
    15

    Re: COUNTIFS - Where Closed date is less than or equal to Open date +7

    Works like a charm. Thank you !!

  6. #6
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    3,237

    Re: COUNTIFS - Where Closed date is less than or equal to Open date +7

    You are welcome, glad to help, 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. [SOLVED] Calculate between dates [Start Date] [Closed Date] but if no close date today()
    By brian_2me in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-11-2021, 09:36 AM
  2. Count of historical ticket by month based upon an open and closed date
    By Mr.TDP in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-14-2021, 12:14 AM
  3. [SOLVED] Cumulative Open & Closed totals by date
    By teastreet in forum Excel General
    Replies: 11
    Last Post: 09-04-2020, 06:39 PM
  4. Need a formula for "Days Open" with a Turn In Date and Date Closed
    By jinthesensei in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-08-2018, 11:31 PM
  5. [SOLVED] If statement to mark cell Open or Closed based on date
    By ap1591 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-03-2018, 05:17 PM
  6. Replies: 0
    Last Post: 11-23-2015, 04:50 AM
  7. Replies: 1
    Last Post: 09-28-2012, 08:52 AM

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