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

1. ## 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",""))

2. ## 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. ## Re: COUNTIFS - Where Closed date is less than or equal to Open date +7

Updated as requested. Thank you.

4. ## 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. ## Re: COUNTIFS - Where Closed date is less than or equal to Open date +7

Works like a charm. Thank you !!

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

You are welcome, glad to help, thanks for the Rep!

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