COUNTIFS End Date that is less than 7 days older than Start Date

Question

Good day all, I'm running into an issue trying to use COUNTIFS to find the number of cells where the [State] column value is "Closed" and [Closed] values (date & time stamps) that are 7 days or less away from the [Opened] date values. In other words, 'work orders that were closed within 7 days'.

The [State],"Closed" portion of the formula works fine, but in trying to count closed dates that are <= to 7 days from the open date is throwing me off. I'm getting spills and they are not in the form of a 1 or 0. I tried wrapping the formula in a =SUM() but because the spill isn't a 1/0 it's just giving me some large number

I'm trying variations of the following formula:

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

I tried to nest an IF statement:
IF(Orders[Opened]+7>=Orders[Closed],"1","")

Which spilled 1's and 0's but the =SUM() didn't work.

Sample data:

State Opened Closed
Closed 11/01/2022 04:57:39 11/04/2022 21:40:24
Closed 11/01/2022 05:05:17 11/01/2022 11:06:06
Closed 11/01/2022 05:13:38 11/04/2022 21:40:19
Closed 11/01/2022 05:25:20 11/09/2022 22:32:17
Pending 11/01/2022 05:25:38 11/04/2022 21:40:21
Closed 11/01/2022 06:20:07 11/07/2022 21:44:35
Closed 11/01/2022 06:24:05 11/03/2022 05:01:37
Hold 11/01/2022 06:32:49 11/08/2022 21:41:00
Closed 11/01/2022 06:45:43 11/04/2022 21:40:23
Closed 11/01/2022 06:45:50 11/10/2022 21:43:29
Closed 11/01/2022 06:49:18 11/07/2022 21:44:38
New 11/01/2022 06:58:56 11/02/2022 08:20:08
Closed 11/01/2022 07:06:32 11/04/2022 21:40:20
Closed 11/01/2022 07:09:28 11/04/2022 12:38:49
Canceled 11/01/2022 07:12:10 11/01/2022 07:20:54
Closed 11/01/2022 07:14:06 11/03/2022 06:23:38
Closed 11/01/2022 07:37:21 11/08/2022 21:41:08

Help is always appreciated.
Forever Formulaically Challenged,
Jason ;)

Answer 1

Excel 365 Pro Plus with Power Query.
Filter <=7 days and "Closed"
No formulas, no VBA macro.
https://www.mediafire.com/file/0amddcvogap85ws/12_03_22.xlsx/file
https://www.mediafire.com/file/f3k60j4sp4bru39/12_03_22.pdf/file

Answer 2

Hi @Jason Stumpf ,
Try this formula:

=SUMPRODUCT(($A$2:$A$18="Closed")*($C$2:$C$18-$B$2:$B$18<=7))  

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.