Sum only when time overlaps
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 |
---|
|
---|
| A | B | C | D | E |
---|
1 | # Checkout Date | Checkin Date | Quantity | Number of overlaps over time | Quantity of overlap |
---|
2 | 3/13/2024 12:11 | 3/13/2024 12:12 | 51 | 1 | |
---|
3 | 3/13/2024 13:31 | 3/13/2024 13:31 | 51 | 1 | |
---|
4 | 3/13/2024 14:28 | 3/13/2024 14:29 | 51 | 1 | |
---|
5 | 3/13/2024 14:33 | 3/13/2024 14:33 | 51 | 1 | |
---|
6 | 3/13/2024 17:23 | 3/13/2024 18:16 | 47 | 1 | |
---|
7 | 3/14/2024 9:33 | 3/14/2024 9:33 | 12 | 1 | |
---|
8 | 3/14/2024 9:33 | 3/14/2024 9:33 | 12 | 1 | |
---|
9 | 3/14/2024 9:34 | 3/14/2024 9:34 | 12 | 1 | |
---|
10 | 3/14/2024 9:34 | 3/14/2024 9:35 | 12 | 1 | |
---|
11 | 3/14/2024 9:49 | 3/14/2024 9:49 | 12 | 1 | |
---|
12 | 3/14/2024 9:52 | 3/14/2024 9:52 | 12 | 1 | |
---|
13 | 3/14/2024 9:53 | 3/14/2024 9:54 | 12 | 1 | |
---|
14 | 3/14/2024 9:55 | 3/14/2024 9:56 | 12 | 1 | |
---|
15 | 3/14/2024 10:00 | 3/14/2024 10:00 | 12 | 1 | |
---|
16 | 3/14/2024 10:02 | 3/14/2024 10:02 | 12 | 1 | |
---|
17 | 3/14/2024 10:04 | 3/14/2024 10:04 | 12 | 1 | |
---|
18 | 3/14/2024 10:13 | 3/14/2024 10:14 | 12 | 1 | |
---|
19 | 3/14/2024 10:43 | 3/14/2024 10:43 | 12 | 1 | |
---|
20 | 3/14/2024 10:54 | 3/14/2024 10:54 | 12 | 1 | |
---|
21 | 3/14/2024 10:59 | 3/14/2024 10:59 | 12 | 1 | |
---|
22 | 3/14/2024 10:59 | 3/14/2024 10:59 | 12 | 1 | |
---|
23 | 3/14/2024 11:01 | 3/14/2024 11:01 | 12 | 1 | |
---|
24 | 3/14/2024 11:04 | 3/14/2024 11:04 | 12 | 1 | |
---|
25 | 3/14/2024 11:27 | 3/14/2024 11:27 | 12 | 1 | |
---|
26 | 3/14/2024 11:29 | 3/14/2024 11:30 | 51 | 1 | |
---|
27 | 3/14/2024 11:35 | 3/14/2024 11:36 | 51 | 1 | |
---|
28 | 3/14/2024 11:36 | 3/14/2024 11:36 | 12 | 1 | |
---|
29 | 3/14/2024 11:44 | 3/14/2024 11:44 | 12 | 1 | |
---|
30 | 3/14/2024 11:50 | 3/14/2024 11:50 | 51 | 1 | |
---|
31 | 3/14/2024 11:51 | 3/14/2024 11:51 | 12 | 1 | |
---|
32 | 3/14/2024 11:53 | 3/14/2024 11:53 | 12 | 1 | |
---|
33 | 3/14/2024 11:55 | 3/14/2024 11:55 | 12 | 1 | |
---|
34 | 3/14/2024 12:01 | 3/14/2024 12:01 | 12 | 1 | |
---|
35 | 3/14/2024 12:04 | 3/14/2024 12:05 | 51 | 1 | |
---|
36 | 3/14/2024 12:15 | 3/14/2024 12:16 | 51 | 1 | |
---|
37 | 3/14/2024 12:15 | 3/14/2024 12:16 | 51 | 2 | |
---|
38 | 3/14/2024 12:16 | 3/14/2024 12:16 | 51 | 2 | |
---|
39 | 3/14/2024 12:26 | 3/14/2024 12:26 | 51 | 1 | |
---|
40 | 3/14/2024 12:26 | 3/14/2024 12:26 | 51 | 2 | |
---|
41 | 3/14/2024 12:26 | 3/14/2024 12:27 | 51 | 3 | |
---|
42 | 3/14/2024 12:37 | 3/14/2024 12:39 | 67 | 1 | |
---|
43 | 3/14/2024 12:50 | 3/14/2024 12:52 | 67 | 1 | |
---|
44 | 3/14/2024 13:17 | 3/14/2024 13:17 | 12 | 1 | |
---|
45 | 3/14/2024 13:29 | 3/14/2024 13:29 | 12 | 1 | |
---|
46 | 3/14/2024 13:33 | 3/14/2024 13:33 | 12 | 1 | |
---|
47 | 3/14/2024 13:42 | 3/14/2024 13:42 | 12 | 1 | |
---|
48 | 3/14/2024 13:43 | 3/14/2024 13:43 | 12 | 1 | |
---|
49 | 3/14/2024 13:43 | 3/14/2024 13:43 | 3 | 2 | |
---|
50 | 3/14/2024 13:43 | 3/14/2024 14:29 | 6 | 2 | |
---|
51 | 3/14/2024 13:48 | 3/14/2024 13:48 | 12 | 2 | |
---|
52 | 3/14/2024 13:51 | 3/14/2024 13:51 | 12 | 2 | |
---|
53 | 3/14/2024 13:57 | 3/14/2024 13:57 | 12 | 2 | |
---|
54 | 3/14/2024 14:29 | 3/14/2024 14:29 | 3 | 2 | |
---|
55 | 3/14/2024 16:09 | 3/14/2024 16:11 | 67 | 1 | |
---|
56 | 3/14/2024 16:13 | 3/14/2024 16:16 | 67 | 1 | |
---|
57 | 3/14/2024 16:14 | 3/14/2024 16:16 | 67 | 2 | |
---|
58 | 3/15/2024 14:09 | 3/15/2024 14:10 | 51 | 1 | |
---|
59 | 3/15/2024 14:50 | 3/15/2024 14:50 | 51 | 1 | |
---|
60 | 3/15/2024 15:07 | 3/15/2024 15:07 | 12 | 1 | |
---|
61 | 3/15/2024 15:17 | 3/15/2024 15:17 | 12 | 1 | |
---|
62 | 3/15/2024 15:19 | 3/15/2024 15:19 | 12 | 1 | |
---|
63 | 3/15/2024 15:35 | 3/15/2024 15:35 | 12 | 1 | |
---|
64 | 3/15/2024 19:25 | 3/16/2024 6:10 | 360 | 1 | |
---|
|
---|
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 |
---|
|
---|
| A | B | C | D | E |
---|
1 | # Checkout Date | Checkin Date | Quantity | Number of overlaps over time | Quantity of overlap |
---|
2 | 2024-03-13 12:11:53 | 2024-03-13 12:12:23 | 51 | 1 | 51 |
---|
3 | 2024-03-13 13:31:15 | 2024-03-13 13:31:19 | 51 | 1 | 51 |
---|
4 | 2024-03-13 14:28:49 | 2024-03-13 14:29:17 | 51 | 1 | 51 |
---|
5 | 2024-03-13 14:33:33 | 2024-03-13 14:33:58 | 51 | 1 | 51 |
---|
6 | 2024-03-13 17:23:04 | 2024-03-13 18:16:58 | 47 | 1 | 47 |
---|
7 | 2024-03-14 09:33:28 | 2024-03-14 09:33:46 | 12 | 1 | 12 |
---|
8 | 2024-03-14 09:33:48 | 2024-03-14 09:33:49 | 12 | 1 | 12 |
---|
9 | 2024-03-14 09:34:25 | 2024-03-14 09:34:27 | 12 | 1 | 12 |
---|
10 | 2024-03-14 09:34:58 | 2024-03-14 09:35:03 | 12 | 1 | 12 |
---|
11 | 2024-03-14 09:49:49 | 2024-03-14 09:49:53 | 12 | 1 | 12 |
---|
12 | 2024-03-14 09:52:35 | 2024-03-14 09:52:38 | 12 | 1 | 12 |
---|
13 | 2024-03-14 09:53:59 | 2024-03-14 09:54:01 | 12 | 1 | 12 |
---|
14 | 2024-03-14 09:55:57 | 2024-03-14 09:56:01 | 12 | 1 | 12 |
---|
15 | 2024-03-14 10:00:19 | 2024-03-14 10:00:47 | 12 | 1 | 12 |
---|
16 | 2024-03-14 10:02:40 | 2024-03-14 10:02:46 | 12 | 1 | 12 |
---|
17 | 2024-03-14 10:04:05 | 2024-03-14 10:04:13 | 12 | 1 | 12 |
---|
18 | 2024-03-14 10:13:58 | 2024-03-14 10:14:01 | 12 | 1 | 12 |
---|
19 | 2024-03-14 10:43:46 | 2024-03-14 10:43:54 | 12 | 1 | 12 |
---|
20 | 2024-03-14 10:54:38 | 2024-03-14 10:54:42 | 12 | 1 | 12 |
---|
21 | 2024-03-14 10:59:06 | 2024-03-14 10:59:07 | 12 | 1 | 12 |
---|
22 | 2024-03-14 10:59:10 | 2024-03-14 10:59:14 | 12 | 1 | 12 |
---|
23 | 2024-03-14 11:01:11 | 2024-03-14 11:01:50 | 12 | 1 | 12 |
---|
24 | 2024-03-14 11:04:13 | 2024-03-14 11:04:17 | 12 | 1 | 12 |
---|
25 | 2024-03-14 11:27:18 | 2024-03-14 11:27:24 | 12 | 1 | 12 |
---|
26 | 2024-03-14 11:29:23 | 2024-03-14 11:30:05 | 51 | 1 | 51 |
---|
27 | 2024-03-14 11:35:39 | 2024-03-14 11:36:09 | 51 | 1 | 51 |
---|
28 | 2024-03-14 11:36:40 | 2024-03-14 11:36:46 | 12 | 1 | 12 |
---|
29 | 2024-03-14 11:44:19 | 2024-03-14 11:44:24 | 12 | 1 | 12 |
---|
30 | 2024-03-14 11:50:20 | 2024-03-14 11:50:47 | 51 | 1 | 51 |
---|
31 | 2024-03-14 11:51:40 | 2024-03-14 11:51:45 | 12 | 1 | 12 |
---|
32 | 2024-03-14 11:53:09 | 2024-03-14 11:53:19 | 12 | 1 | 12 |
---|
33 | 2024-03-14 11:55:36 | 2024-03-14 11:55:38 | 12 | 1 | 12 |
---|
34 | 2024-03-14 12:01:57 | 2024-03-14 12:01:59 | 12 | 1 | 12 |
---|
35 | 2024-03-14 12:04:57 | 2024-03-14 12:05:28 | 51 | 1 | 51 |
---|
36 | 2024-03-14 12:15:46 | 2024-03-14 12:16:08 | 51 | 1 | 51 |
---|
37 | 2024-03-14 12:15:59 | 2024-03-14 12:16:32 | 51 | 2 | 102 |
---|
38 | 2024-03-14 12:16:10 | 2024-03-14 12:16:45 | 51 | 2 | 102 |
---|
39 | 2024-03-14 12:26:14 | 2024-03-14 12:26:41 | 51 | 1 | 51 |
---|
40 | 2024-03-14 12:26:22 | 2024-03-14 12:26:47 | 51 | 2 | 102 |
---|
41 | 2024-03-14 12:26:41 | 2024-03-14 12:27:09 | 51 | 3 | 153 |
---|
42 | 2024-03-14 12:37:07 | 2024-03-14 12:39:21 | 67 | 1 | 67 |
---|
43 | 2024-03-14 12:50:30 | 2024-03-14 12:52:17 | 67 | 1 | 67 |
---|
44 | 2024-03-14 13:17:30 | 2024-03-14 13:17:38 | 12 | 1 | 12 |
---|
45 | 2024-03-14 13:29:48 | 2024-03-14 13:29:53 | 12 | 1 | 12 |
---|
46 | 2024-03-14 13:33:03 | 2024-03-14 13:33:05 | 12 | 1 | 12 |
---|
47 | 2024-03-14 13:42:11 | 2024-03-14 13:42:12 | 12 | 1 | 12 |
---|
48 | 2024-03-14 13:43:40 | 2024-03-14 13:43:41 | 12 | 1 | 12 |
---|
49 | 2024-03-14 13:43:44 | 2024-03-14 13:43:44 | 3 | 2 | 9 |
---|
50 | 2024-03-14 13:43:44 | 2024-03-14 14:29:07 | 6 | 2 | 9 |
---|
51 | 2024-03-14 13:48:27 | 2024-03-14 13:48:28 | 12 | 2 | 18 |
---|
52 | 2024-03-14 13:51:10 | 2024-03-14 13:51:13 | 12 | 2 | 18 |
---|
53 | 2024-03-14 13:57:09 | 2024-03-14 13:57:10 | 12 | 2 | 18 |
---|
54 | 2024-03-14 14:29:07 | 2024-03-14 14:29:07 | 3 | 2 | 9 |
---|
55 | 2024-03-14 16:09:34 | 2024-03-14 16:11:17 | 67 | 1 | 67 |
---|
56 | 2024-03-14 16:13:44 | 2024-03-14 16:16:03 | 67 | 1 | 67 |
---|
57 | 2024-03-14 16:14:25 | 2024-03-14 16:16:10 | 67 | 2 | 134 |
---|
58 | 2024-03-15 14:09:50 | 2024-03-15 14:10:23 | 51 | 1 | 51 |
---|
59 | 2024-03-15 14:50:02 | 2024-03-15 14:50:30 | 51 | 1 | 51 |
---|
60 | 2024-03-15 15:07:37 | 2024-03-15 15:07:39 | 12 | 1 | 12 |
---|
61 | 2024-03-15 15:17:30 | 2024-03-15 15:17:32 | 12 | 1 | 12 |
---|
62 | 2024-03-15 15:19:03 | 2024-03-15 15:19:06 | 12 | 1 | 12 |
---|
63 | 2024-03-15 15:35:23 | 2024-03-15 15:35:26 | 12 | 1 | 12 |
---|
64 | 2024-03-15 19:25:07 | 2024-03-16 06:10:09 | 360 | 1 | 360 |
---|
|
---|