Sum only when time overlaps

J

joaotcosta23

New Member
Joined
May 20, 2024
Messages
1
Office Version
  1. Prefer Not To Say
For example in the table below, for each row I would like column E to display the sum of column C for those occasions where the start time (A) and end time (B) are overlapping.
(In column C I'm calculating the number of overlaps at a given point in time, and that works perfectly)
SDSL2CAD_usage.xlsx
ABCDE
1# Checkout DateCheckin DateQuantityNumber of overlaps over timeQuantity of overlap
23/13/2024 12:113/13/2024 12:12511
33/13/2024 13:313/13/2024 13:31511
43/13/2024 14:283/13/2024 14:29511
53/13/2024 14:333/13/2024 14:33511
63/13/2024 17:233/13/2024 18:16471
73/14/2024 9:333/14/2024 9:33121
83/14/2024 9:333/14/2024 9:33121
93/14/2024 9:343/14/2024 9:34121
103/14/2024 9:343/14/2024 9:35121
113/14/2024 9:493/14/2024 9:49121
123/14/2024 9:523/14/2024 9:52121
133/14/2024 9:533/14/2024 9:54121
143/14/2024 9:553/14/2024 9:56121
153/14/2024 10:003/14/2024 10:00121
163/14/2024 10:023/14/2024 10:02121
173/14/2024 10:043/14/2024 10:04121
183/14/2024 10:133/14/2024 10:14121
193/14/2024 10:433/14/2024 10:43121
203/14/2024 10:543/14/2024 10:54121
213/14/2024 10:593/14/2024 10:59121
223/14/2024 10:593/14/2024 10:59121
233/14/2024 11:013/14/2024 11:01121
243/14/2024 11:043/14/2024 11:04121
253/14/2024 11:273/14/2024 11:27121
263/14/2024 11:293/14/2024 11:30511
273/14/2024 11:353/14/2024 11:36511
283/14/2024 11:363/14/2024 11:36121
293/14/2024 11:443/14/2024 11:44121
303/14/2024 11:503/14/2024 11:50511
313/14/2024 11:513/14/2024 11:51121
323/14/2024 11:533/14/2024 11:53121
333/14/2024 11:553/14/2024 11:55121
343/14/2024 12:013/14/2024 12:01121
353/14/2024 12:043/14/2024 12:05511
363/14/2024 12:153/14/2024 12:16511
373/14/2024 12:153/14/2024 12:16512
383/14/2024 12:163/14/2024 12:16512
393/14/2024 12:263/14/2024 12:26511
403/14/2024 12:263/14/2024 12:26512
413/14/2024 12:263/14/2024 12:27513
423/14/2024 12:373/14/2024 12:39671
433/14/2024 12:503/14/2024 12:52671
443/14/2024 13:173/14/2024 13:17121
453/14/2024 13:293/14/2024 13:29121
463/14/2024 13:333/14/2024 13:33121
473/14/2024 13:423/14/2024 13:42121
483/14/2024 13:433/14/2024 13:43121
493/14/2024 13:433/14/2024 13:4332
503/14/2024 13:433/14/2024 14:2962
513/14/2024 13:483/14/2024 13:48122
523/14/2024 13:513/14/2024 13:51122
533/14/2024 13:573/14/2024 13:57122
543/14/2024 14:293/14/2024 14:2932
553/14/2024 16:093/14/2024 16:11671
563/14/2024 16:133/14/2024 16:16671
573/14/2024 16:143/14/2024 16:16672
583/15/2024 14:093/15/2024 14:10511
593/15/2024 14:503/15/2024 14:50511
603/15/2024 15:073/15/2024 15:07121
613/15/2024 15:173/15/2024 15:17121
623/15/2024 15:193/15/2024 15:19121
633/15/2024 15:353/15/2024 15:35121
643/15/2024 19:253/16/2024 6:103601
For Forum
Cell Formulas
RangeFormula
D2:D64D2=SUMPRODUCT(--($A$2:$A$8147<=A2),--($B$2:$B$8147>=A2))
 
Sort by date Sort by votes
Welcome to the forum. Please explain what you mean by overlap? You have independent time ranges so there are not many (as shown by your own formula). But, editing your formula to get the amounts you ask for, try this:
Book1
ABCDE
1# Checkout DateCheckin DateQuantityNumber of overlaps over timeQuantity of overlap
22024-03-13 12:11:532024-03-13 12:12:2351151
32024-03-13 13:31:152024-03-13 13:31:1951151
42024-03-13 14:28:492024-03-13 14:29:1751151
52024-03-13 14:33:332024-03-13 14:33:5851151
62024-03-13 17:23:042024-03-13 18:16:5847147
72024-03-14 09:33:282024-03-14 09:33:4612112
82024-03-14 09:33:482024-03-14 09:33:4912112
92024-03-14 09:34:252024-03-14 09:34:2712112
102024-03-14 09:34:582024-03-14 09:35:0312112
112024-03-14 09:49:492024-03-14 09:49:5312112
122024-03-14 09:52:352024-03-14 09:52:3812112
132024-03-14 09:53:592024-03-14 09:54:0112112
142024-03-14 09:55:572024-03-14 09:56:0112112
152024-03-14 10:00:192024-03-14 10:00:4712112
162024-03-14 10:02:402024-03-14 10:02:4612112
172024-03-14 10:04:052024-03-14 10:04:1312112
182024-03-14 10:13:582024-03-14 10:14:0112112
192024-03-14 10:43:462024-03-14 10:43:5412112
202024-03-14 10:54:382024-03-14 10:54:4212112
212024-03-14 10:59:062024-03-14 10:59:0712112
222024-03-14 10:59:102024-03-14 10:59:1412112
232024-03-14 11:01:112024-03-14 11:01:5012112
242024-03-14 11:04:132024-03-14 11:04:1712112
252024-03-14 11:27:182024-03-14 11:27:2412112
262024-03-14 11:29:232024-03-14 11:30:0551151
272024-03-14 11:35:392024-03-14 11:36:0951151
282024-03-14 11:36:402024-03-14 11:36:4612112
292024-03-14 11:44:192024-03-14 11:44:2412112
302024-03-14 11:50:202024-03-14 11:50:4751151
312024-03-14 11:51:402024-03-14 11:51:4512112
322024-03-14 11:53:092024-03-14 11:53:1912112
332024-03-14 11:55:362024-03-14 11:55:3812112
342024-03-14 12:01:572024-03-14 12:01:5912112
352024-03-14 12:04:572024-03-14 12:05:2851151
362024-03-14 12:15:462024-03-14 12:16:0851151
372024-03-14 12:15:592024-03-14 12:16:32512102
382024-03-14 12:16:102024-03-14 12:16:45512102
392024-03-14 12:26:142024-03-14 12:26:4151151
402024-03-14 12:26:222024-03-14 12:26:47512102
412024-03-14 12:26:412024-03-14 12:27:09513153
422024-03-14 12:37:072024-03-14 12:39:2167167
432024-03-14 12:50:302024-03-14 12:52:1767167
442024-03-14 13:17:302024-03-14 13:17:3812112
452024-03-14 13:29:482024-03-14 13:29:5312112
462024-03-14 13:33:032024-03-14 13:33:0512112
472024-03-14 13:42:112024-03-14 13:42:1212112
482024-03-14 13:43:402024-03-14 13:43:4112112
492024-03-14 13:43:442024-03-14 13:43:44329
502024-03-14 13:43:442024-03-14 14:29:07629
512024-03-14 13:48:272024-03-14 13:48:2812218
522024-03-14 13:51:102024-03-14 13:51:1312218
532024-03-14 13:57:092024-03-14 13:57:1012218
542024-03-14 14:29:072024-03-14 14:29:07329
552024-03-14 16:09:342024-03-14 16:11:1767167
562024-03-14 16:13:442024-03-14 16:16:0367167
572024-03-14 16:14:252024-03-14 16:16:10672134
582024-03-15 14:09:502024-03-15 14:10:2351151
592024-03-15 14:50:022024-03-15 14:50:3051151
602024-03-15 15:07:372024-03-15 15:07:3912112
612024-03-15 15:17:302024-03-15 15:17:3212112
622024-03-15 15:19:032024-03-15 15:19:0612112
632024-03-15 15:35:232024-03-15 15:35:2612112
642024-03-15 19:25:072024-03-16 06:10:093601360
Sheet1
Cell Formulas
RangeFormula
D2:D64D2=SUMPRODUCT(($A$2:$A$64<=A2)*($B$2:$B$64>=A2))
E2:E64E2=SUMPRODUCT(($A$2:$A$64<=A2)*($B$2:$B$64>=A2)*($C$2:$C$64))
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
VBA Separate Day and Time to Another Column if condition meet
2
Replies
10
Views
160
SamKhem
S
B
  • Question Question
VBA Combining Information and Totaling a Sum
Replies
7
Views
86
DanteAmor
DanteAmor
B
  • Solved
timestamp listing to show more than >19hrs away per day?
Replies
9
Views
237
Boboka
B
E
  • Question Question
Formula to Covert date into a specific day of the week
Replies
2
Views
128
rpaulson
rpaulson
H
  • Solved
Export some data if a condition is met.
Replies
2
Views
99
harzer
H

Forum statistics

Threads
1,226,834
Messages
6,193,216
Members
453,780
Latest member
enghoss77

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top