Sumif between two times

[DDT123]

Greetings all! I have the below spreadsheet that I'm needing to sum total "paused*" times between two intervals. Some of the paused times bleed over into the following intervals. For example, paused time at 8:18 AM was partially during the 8:15 AM interval and carried thru to the 9:30 AM interval. So total paused time for those intervals should be 15 minutes each. Any help is greatly appreciated!!

Book1
	A	B	C	D	E	F	G	H
1	Description	Transaction Start	Transaction End	Duration			Interval Start	Sum
2	active	10/27/2021 7:56	10/27/2021 8:18	0:22:31			8:00 AM
3	paused - other	10/27/2021 8:18	10/27/2021 9:38	1:19:41			8:15 AM
4	active	10/27/2021 9:38	10/27/2021 9:39	0:01:05			8:30 AM
5	paused - Restroom	10/27/2021 9:39	10/27/2021 9:39	0:00:15			8:45 AM
6	paused - other	10/27/2021 9:39	10/27/2021 9:45	0:05:25			9:00 AM
7	paused - Break	10/27/2021 9:45	10/27/2021 10:00	0:15:11			9:15 AM
8	active	10/27/2021 10:00	10/27/2021 10:01	0:00:28			9:30 AM
9	ringing	10/27/2021 10:01	10/27/2021 10:01	0:00:03			9:45 AM
10	on call	10/27/2021 10:01	10/27/2021 10:05	0:04:50			10:00 AM
11	wrap up	10/27/2021 10:05	10/27/2021 10:05	0:00:06			10:15 AM
12	paused - Break	10/27/2021 10:05	10/27/2021 10:06	0:00:09			10:30 AM
13	paused - other	10/27/2021 10:06	10/27/2021 11:19	1:13:37			10:45 AM
14	active	10/27/2021 11:19	10/27/2021 11:24	0:04:54			11:00 AM
15	ringing	10/27/2021 11:24	10/27/2021 11:24	0:00:04			11:15 AM
16	on call	10/27/2021 11:24	10/27/2021 11:31	0:06:17			11:30 AM
17	wrap up	10/27/2021 11:31	10/27/2021 11:31	0:00:19			11:45 AM
18	paused - other	10/27/2021 11:31	10/27/2021 12:32	1:00:58			12:00 PM
19	paused - Lunch	10/27/2021 12:32	10/27/2021 13:03	0:30:56			12:15 PM
20	active	10/27/2021 13:03	10/27/2021 13:03	0:00:30			12:30 PM
21	paused - Lunch	10/27/2021 13:03	10/27/2021 13:03	0:00:12			12:45 PM
22	paused - Processing	10/27/2021 13:03	10/27/2021 15:04	2:00:51			1:00 PM
23	active	10/27/2021 15:04	10/27/2021 15:05	0:00:17			1:15 PM
24	ringing	10/27/2021 15:05	10/27/2021 15:05	0:00:04			1:30 PM
25	on call	10/27/2021 15:05	10/27/2021 15:47	0:42:39			1:45 PM
26	wrap up	10/27/2021 15:47	10/27/2021 15:48	0:00:14			2:00 PM
27	paused - Break	10/27/2021 15:48	10/27/2021 16:03	0:15:12			2:15 PM
28	active	10/27/2021 16:03	10/27/2021 16:04	0:01:28			2:30 PM
29	paused - Processing	10/27/2021 16:04	10/27/2021 16:35	0:30:47			2:45 PM
30							3:00 PM
31							3:15 PM
32							3:30 PM
33							3:45 PM
Sheet1

 

[jasonb75]

Will the data to be summarised always be for a single day as per your example?

Can a single pause start before midnight and finish after midnight?

 

[DDT123]

Will the data to be summarised always be for a single day as per your example?

Can a single pause start before midnight and finish after midnight?

Yes, the report will be ran daily, and we'll be looking at the total paused time per interval. The pauses (intervals) will begin at 8 AM and the final interval will be 8 PM.
The example above is for 1 agent, so with all the agents in the full raw report we will need the sum of all agents during interval x. So the 9:00 AM to 9:15 interval may sum to be over 20 hours if multiple agents goes into "pause - break" at the same time.

 

[jasonb75]

This has taken multiple failed attempts but I think that I finally have it right. Try this formula in H2 (based on your example) and fill down.

=LET(i,--"00:15",ts,MOD($B$2:$B$29,1),te,MOD($C$2:$C$29,1),is,G2,ie,G2+i,p,ISNUMBER(SEARCH("paused",$A$2:$A$29)),
SUM(IF(p,IF(ts<=is,IF(te>=ie,i,IF(te>is,te-is)),IF(ts>is,IF(te<ie,te-ts,IF(ts<ie,ie-ts)))))))