Sum items only where times are overlapping

[nicholaspars]

I have seen many ways to determine overlappping times, notably using SUMPRODUCT. In my case I want to take this a step further, where I can add values of another column for each row where the start and end times overlap.

For example in the table below, for each row I would like column D to display the sum of column C for those occasions where the start time (A) and end time (B) are overlapping.

Many thanks for any pointers in how to go about this.

 

[jasonb75]

Try

=SUMIFS(C:C,A:A,"<="&B2,B:B,">="&A2)

Not sure if that's right or not, I know in theory what I'm thinking but formulas can't be tested on screen captures (XL2BB is a much better way to post data samples, see link in my signature block below).

 

[nicholaspars]

Thanks Jason

I tried the formula and have attached via XL2BB, but doesn't look quite right, for example the jump in row 6 from $4,726 to $28,700 in row 7 is not feasible.

Book1
	A	B	C	D
1	START TIME	END TIME	Cost	SUM OF OVERLAP
2	09/11/2020 15:31:24	09/11/2020 16:04:35	$1,240.00	$4,163.00
3	09/11/2020 15:31:24	09/11/2020 20:04:47	$620.00	$8,459.00
4	09/11/2020 15:36:33	09/11/2020 15:49:35	$1,012.00	$3,378.00
5	09/11/2020 15:36:33	09/11/2020 16:20:57	$253.00	$4,766.00
6	09/11/2020 15:36:33	13/11/2020 22:20:00	$253.00	$28,700.00
7	09/11/2020 15:58:15	13/11/2020 22:20:00	$785.00	$27,688.00
8	09/11/2020 16:05:11	13/11/2020 22:20:00	$603.00	$26,448.00
9	09/11/2020 16:29:43	09/11/2020 19:18:36	$1,080.00	$4,814.00
10	09/11/2020 16:31:36	13/11/2020 22:20:00	$1,080.00	$26,195.00
11	09/11/2020 17:23:07	13/11/2020 22:20:00	$393.00	$26,195.00
12	09/11/2020 19:18:59	09/11/2020 21:21:40	$1,140.00	$5,260.00
13	09/11/2020 20:10:09	10/11/2020 18:34:37	$386.00	$8,859.00
14	09/11/2020 21:22:40	10/11/2020 18:34:37	$196.00	$7,719.00
15	09/11/2020 21:26:51	09/11/2020 22:06:29	$692.00	$4,388.00
16	09/11/2020 22:06:53	10/11/2020 15:39:54	$326.00	$5,000.00
17	09/11/2020 22:06:53	10/11/2020 16:34:18	$326.00	$5,972.00
18	09/11/2020 22:06:53	10/11/2020 20:38:47	$652.00	$7,923.00
19	10/11/2020 15:55:51	10/11/2020 16:05:53	$466.00	$5,140.00
20	10/11/2020 16:23:32	10/11/2020 20:25:31	$506.00	$6,433.00
21	10/11/2020 16:55:04	10/11/2020 17:50:32	$465.00	$5,319.00
22	10/11/2020 18:33:33	10/11/2020 19:23:51	$354.00	$5,642.00
23	10/11/2020 18:33:33	10/11/2020 20:17:18	$236.00	$5,642.00
24	10/11/2020 19:08:45	10/11/2020 20:17:18	$198.00	$5,060.00
25	10/11/2020 20:26:18	10/11/2020 21:58:23	$698.00	$4,899.00
26	10/11/2020 21:28:39	10/11/2020 21:59:22	$435.00	$4,247.00
27	11/11/2020 15:33:25	11/11/2020 15:35:57	$337.33	$4,126.00
28	11/11/2020 15:33:25	11/11/2020 16:33:06	$674.67	$5,506.00
29	11/11/2020 15:38:37	11/11/2020 19:16:19	$581.00	$5,956.67
30	11/11/2020 15:38:37	11/11/2020 21:58:40	$581.00	$8,427.67
31	11/11/2020 16:05:25	11/11/2020 20:44:39	$218.00	$6,206.67
32	11/11/2020 16:41:10	11/11/2020 21:02:51	$260.00	$6,147.00
33	11/11/2020 16:41:10	11/11/2020 21:41:45	$390.00	$7,243.00
34	11/11/2020 19:10:39	13/11/2020 21:06:00	$138.00	$16,407.00
35	11/11/2020 19:30:52	11/11/2020 21:06:12	$250.00	$6,172.00
36	11/11/2020 20:49:37	11/11/2020 21:56:33	$205.00	$6,954.00
37	11/11/2020 20:49:37	13/11/2020 22:20:00	$410.00	$15,608.00
38	11/11/2020 21:04:08	11/11/2020 21:59:00	$202.00	$6,694.00
39	11/11/2020 21:04:08	13/11/2020 22:20:00	$404.00	$15,348.00
40	11/11/2020 21:35:59	11/11/2020 21:49:42	$490.00	$5,934.00
41	11/11/2020 21:50:45	12/11/2020 16:02:36	$170.00	$8,723.00
42	11/11/2020 21:50:45	12/11/2020 21:35:37	$340.00	$11,278.00
43	12/11/2020 15:33:50	12/11/2020 15:37:00	$315.00	$6,365.00
44	12/11/2020 15:33:50	12/11/2020 15:53:01	$630.00	$7,735.00
45	12/11/2020 15:36:44	12/11/2020 21:59:42	$211.00	$10,290.00
46	12/11/2020 15:36:44	13/11/2020 22:20:00	$633.00	$13,230.00
47	12/11/2020 15:42:13	12/11/2020 21:52:25	$580.00	$9,975.00
48	12/11/2020 15:45:22	12/11/2020 16:37:43	$395.00	$8,115.00
49	12/11/2020 15:45:22	13/11/2020 22:20:00	$395.00	$12,915.00
50	12/11/2020 16:20:41	12/11/2020 21:59:05	$695.00	$9,175.00
51	12/11/2020 16:49:34	12/11/2020 21:59:59	$520.00	$8,780.00
52	12/11/2020 17:58:55	13/11/2020 22:20:00	$395.00	$11,720.00
53	12/11/2020 19:21:55	13/11/2020 22:20:00	$405.00	$11,720.00
54	12/11/2020 19:33:31	12/11/2020 21:58:38	$540.00	$8,780.00
55	13/11/2020 15:30:10	13/11/2020 22:20:00	$618.00	$8,834.00
56	13/11/2020 15:30:41	13/11/2020 22:20:00	$270.00	$8,834.00
57	13/11/2020 15:36:55	13/11/2020 22:20:00	$584.00	$8,834.00
58	13/11/2020 16:00:31	13/11/2020 22:20:00	$206.00	$8,834.00
59	13/11/2020 16:46:45	13/11/2020 19:04:58	$140.00	$8,132.00
60	13/11/2020 16:46:45	13/11/2020 19:26:11	$280.00	$8,258.00
61	13/11/2020 16:49:00	13/11/2020 21:31:43	$140.00	$8,834.00
62	13/11/2020 19:11:01	13/11/2020 21:35:51	$126.00	$8,694.00
63	13/11/2020 19:26:26	13/11/2020 21:25:09	$576.00	$8,414.00
64	16/11/2020 15:30:29	16/11/2020 15:38:03	$860.00	$2,997.00
65	16/11/2020 15:31:06	16/11/2020 16:29:01	$1,124.00	$3,577.00
66	16/11/2020 15:35:58	17/11/2020 16:29:23	$1,013.00	$8,196.00
67	16/11/2020 15:44:13	17/11/2020 21:59:10	$580.00	$11,242.00
68	16/11/2020 16:30:07	17/11/2020 20:43:00	$650.00	$8,752.00
69	16/11/2020 16:39:57	16/11/2020 18:02:21	$410.33	$3,474.00
70	16/11/2020 16:39:57	16/11/2020 20:47:19	$410.33	$4,170.00
71	16/11/2020 16:39:57	16/11/2020 21:35:43	$410.33	$4,605.00
72	16/11/2020 19:42:21	16/11/2020 21:40:02	$232.00	$4,194.67
73	16/11/2020 19:42:21	16/11/2020 21:57:10	$464.00	$4,194.67
74	16/11/2020 20:48:43	16/11/2020 21:54:38	$435.00	$3,784.33
75	17/11/2020 15:30:53	17/11/2020 16:22:19	$777.00	$3,850.00
76	17/11/2020 15:33:07	17/11/2020 21:51:57	$830.00	$7,174.00
77	17/11/2020 16:42:15	17/11/2020 20:55:09	$1,495.00	$4,600.00
78	17/11/2020 16:45:13	19/11/2020 20:25:16	$370.00	$10,508.00
79	17/11/2020 16:45:13	20/11/2020 15:45:08	$370.00	$10,508.00
80	17/11/2020 17:39:03	17/11/2020 20:35:17	$305.00	$4,600.00
81	17/11/2020 21:25:02	17/11/2020 22:02:40	$784.00	$3,516.00
82	17/11/2020 21:53:46	20/11/2020 22:20:00	$582.00	$7,228.00
83	18/11/2020 15:32:51	20/11/2020 22:20:00	$1,250.00	$5,864.00
84	18/11/2020 15:41:43	18/11/2020 17:50:00	$242.00	$4,448.00
85	18/11/2020 15:41:43	18/11/2020 18:05:06	$242.00	$5,024.00
86	18/11/2020 16:03:06	18/11/2020 17:11:08	$586.00	$3,996.00
87	18/11/2020 16:49:57	20/11/2020 22:20:00	$354.00	$5,864.00
88	18/11/2020 17:30:23	18/11/2020 17:52:50	$452.00	$4,438.00
89	18/11/2020 17:50:36	18/11/2020 18:08:26	$192.00	$4,196.00
90	18/11/2020 17:50:36	18/11/2020 19:21:49	$192.00	$5,036.00
91	18/11/2020 17:50:36	18/11/2020 19:49:59	$192.00	$5,036.00
92	18/11/2020 18:26:19	18/11/2020 19:14:17	$840.00	$4,150.00
Sheet1

Cell Formulas
Range	Formula
D2:D92	D2	=SUMIFS([Cost],[START TIME],"<="&[@[END TIME]],[END TIME],">="&[@[START TIME]])

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D92	Expression	=AND($B2="",$A2<>"")	text	NO

 

[sandy666]

is that what you want?

START TIME	END TIME	Cost
09/11/2020 15:31:24	09/11/2020 16:04:35	$ 1,240.00
09/11/2020 15:31:24	09/11/2020 20:04:47	$ 620.00		$ 1,860.00
09/11/2020 15:36:33	09/11/2020 15:49:35	$ 1,012.00		$ 1,632.00
09/11/2020 15:36:33	09/11/2020 16:20:57	$ 253.00		$ 1,265.00
09/11/2020 15:36:33	13/11/2020 22:20:00	$ 253.00		$ 506.00
09/11/2020 15:58:15	13/11/2020 22:20:00	$ 785.00		$ 1,038.00
09/11/2020 16:05:11	13/11/2020 22:20:00	$ 603.00		$ 1,388.00
09/11/2020 16:29:43	09/11/2020 19:18:36	$ 1,080.00		$ 1,683.00
09/11/2020 16:31:36	13/11/2020 22:20:00	$ 1,080.00		$ 2,160.00
09/11/2020 17:23:07	13/11/2020 22:20:00	$ 393.00		$ 1,473.00
09/11/2020 19:18:59	09/11/2020 21:21:40	$ 1,140.00		$ 1,533.00
09/11/2020 20:10:09	10/11/2020 18:34:37	$ 386.00		$ 1,526.00
09/11/2020 21:22:40	10/11/2020 18:34:37	$ 196.00		$ 582.00
09/11/2020 21:26:51	09/11/2020 22:06:29	$ 692.00		$ 888.00
09/11/2020 22:06:53	10/11/2020 15:39:54	$ 326.00
09/11/2020 22:06:53	10/11/2020 16:34:18	$ 326.00		$ 652.00
09/11/2020 22:06:53	10/11/2020 20:38:47	$ 652.00		$ 978.00
10/11/2020 15:55:51	10/11/2020 16:05:53	$ 466.00		$ 1,118.00
10/11/2020 16:23:32	10/11/2020 20:25:31	$ 506.00
10/11/2020 16:55:04	10/11/2020 17:50:32	$ 465.00		$ 971.00
10/11/2020 18:33:33	10/11/2020 19:23:51	$ 354.00
10/11/2020 18:33:33	10/11/2020 20:17:18	$ 236.00		$ 590.00
10/11/2020 19:08:45	10/11/2020 20:17:18	$ 198.00		$ 434.00
10/11/2020 20:26:18	10/11/2020 21:58:23	$ 698.00
10/11/2020 21:28:39	10/11/2020 21:59:22	$ 435.00		$ 1,133.00
11/11/2020 15:33:25	11/11/2020 15:35:57	$ 337.33
11/11/2020 15:33:25	11/11/2020 16:33:06	$ 674.67		$ 1,012.00
11/11/2020 15:38:37	11/11/2020 19:16:19	$ 581.00		$ 1,255.67
11/11/2020 15:38:37	11/11/2020 21:58:40	$ 581.00		$ 1,162.00
11/11/2020 16:05:25	11/11/2020 20:44:39	$ 218.00		$ 799.00
11/11/2020 16:41:10	11/11/2020 21:02:51	$ 260.00		$ 478.00
11/11/2020 16:41:10	11/11/2020 21:41:45	$ 390.00		$ 650.00
11/11/2020 19:10:39	13/11/2020 21:06:00	$ 138.00		$ 528.00
11/11/2020 19:30:52	11/11/2020 21:06:12	$ 250.00		$ 388.00
11/11/2020 20:49:37	11/11/2020 21:56:33	$ 205.00		$ 455.00
11/11/2020 20:49:37	13/11/2020 22:20:00	$ 410.00		$ 615.00
11/11/2020 21:04:08	11/11/2020 21:59:00	$ 202.00		$ 612.00
11/11/2020 21:04:08	13/11/2020 22:20:00	$ 404.00		$ 606.00
11/11/2020 21:35:59	11/11/2020 21:49:42	$ 490.00		$ 894.00
11/11/2020 21:50:45	12/11/2020 16:02:36	$ 170.00
11/11/2020 21:50:45	12/11/2020 21:35:37	$ 340.00		$ 510.00
12/11/2020 15:33:50	12/11/2020 15:37:00	$ 315.00		$ 655.00
12/11/2020 15:33:50	12/11/2020 15:53:01	$ 630.00		$ 945.00
12/11/2020 15:36:44	12/11/2020 21:59:42	$ 211.00		$ 841.00
12/11/2020 15:36:44	13/11/2020 22:20:00	$ 633.00		$ 844.00
12/11/2020 15:42:13	12/11/2020 21:52:25	$ 580.00		$ 1,213.00
12/11/2020 15:45:22	12/11/2020 16:37:43	$ 395.00		$ 975.00
12/11/2020 15:45:22	13/11/2020 22:20:00	$ 395.00		$ 790.00
12/11/2020 16:20:41	12/11/2020 21:59:05	$ 695.00		$ 1,090.00
12/11/2020 16:49:34	12/11/2020 21:59:59	$ 520.00		$ 1,215.00
12/11/2020 17:58:55	13/11/2020 22:20:00	$ 395.00		$ 915.00
12/11/2020 19:21:55	13/11/2020 22:20:00	$ 405.00		$ 800.00
12/11/2020 19:33:31	12/11/2020 21:58:38	$ 540.00		$ 945.00
13/11/2020 15:30:10	13/11/2020 22:20:00	$ 618.00
13/11/2020 15:30:41	13/11/2020 22:20:00	$ 270.00		$ 888.00
13/11/2020 15:36:55	13/11/2020 22:20:00	$ 584.00		$ 854.00
13/11/2020 16:00:31	13/11/2020 22:20:00	$ 206.00		$ 790.00
13/11/2020 16:46:45	13/11/2020 19:04:58	$ 140.00		$ 346.00
13/11/2020 16:46:45	13/11/2020 19:26:11	$ 280.00		$ 420.00
13/11/2020 16:49:00	13/11/2020 21:31:43	$ 140.00		$ 420.00
13/11/2020 19:11:01	13/11/2020 21:35:51	$ 126.00		$ 266.00
13/11/2020 19:26:26	13/11/2020 21:25:09	$ 576.00		$ 702.00
16/11/2020 15:30:29	16/11/2020 15:38:03	$ 860.00
16/11/2020 15:31:06	16/11/2020 16:29:01	$ 1,124.00		$ 1,984.00
16/11/2020 15:35:58	17/11/2020 16:29:23	$ 1,013.00		$ 2,137.00
16/11/2020 15:44:13	17/11/2020 21:59:10	$ 580.00		$ 1,593.00
16/11/2020 16:30:07	17/11/2020 20:43:00	$ 650.00		$ 1,230.00
16/11/2020 16:39:57	16/11/2020 18:02:21	$ 410.33		$ 1,060.33
16/11/2020 16:39:57	16/11/2020 20:47:19	$ 410.33		$ 820.67
16/11/2020 16:39:57	16/11/2020 21:35:43	$ 410.33		$ 820.67
16/11/2020 19:42:21	16/11/2020 21:40:02	$ 232.00		$ 642.33
16/11/2020 19:42:21	16/11/2020 21:57:10	$ 464.00		$ 696.00
16/11/2020 20:48:43	16/11/2020 21:54:38	$ 435.00		$ 899.00
17/11/2020 15:30:53	17/11/2020 16:22:19	$ 777.00
17/11/2020 15:33:07	17/11/2020 21:51:57	$ 830.00		$ 1,607.00
17/11/2020 16:42:15	17/11/2020 20:55:09	$ 1,495.00		$ 2,325.00
17/11/2020 16:45:13	19/11/2020 20:25:16	$ 370.00		$ 1,865.00
17/11/2020 16:45:13	20/11/2020 15:45:08	$ 370.00		$ 740.00
17/11/2020 17:39:03	17/11/2020 20:35:17	$ 305.00		$ 675.00
17/11/2020 21:25:02	17/11/2020 22:02:40	$ 784.00
17/11/2020 21:53:46	20/11/2020 22:20:00	$ 582.00		$ 1,366.00
18/11/2020 15:32:51	20/11/2020 22:20:00	$ 1,250.00		$ 1,832.00
18/11/2020 15:41:43	18/11/2020 17:50:00	$ 242.00		$ 1,492.00
18/11/2020 15:41:43	18/11/2020 18:05:06	$ 242.00		$ 484.00
18/11/2020 16:03:06	18/11/2020 17:11:08	$ 586.00		$ 828.00
18/11/2020 16:49:57	20/11/2020 22:20:00	$ 354.00		$ 940.00
18/11/2020 17:30:23	18/11/2020 17:52:50	$ 452.00		$ 806.00
18/11/2020 17:50:36	18/11/2020 18:08:26	$ 192.00		$ 644.00
18/11/2020 17:50:36	18/11/2020 19:21:49	$ 192.00		$ 384.00
18/11/2020 17:50:36	18/11/2020 19:49:59	$ 192.00		$ 384.00
18/11/2020 18:26:19	18/11/2020 19:14:17	$ 840.00		$ 1,032.00

 

[nicholaspars]

is that what you want?

START TIME	END TIME	Cost
09/11/2020 15:31:24	09/11/2020 16:04:35	$ 1,240.00
09/11/2020 15:31:24	09/11/2020 20:04:47	$ 620.00		$ 1,860.00
09/11/2020 15:36:33	09/11/2020 15:49:35	$ 1,012.00		$ 1,632.00
09/11/2020 15:36:33	09/11/2020 16:20:57	$ 253.00		$ 1,265.00
09/11/2020 15:36:33	13/11/2020 22:20:00	$ 253.00		$ 506.00
09/11/2020 15:58:15	13/11/2020 22:20:00	$ 785.00		$ 1,038.00
09/11/2020 16:05:11	13/11/2020 22:20:00	$ 603.00		$ 1,388.00
09/11/2020 16:29:43	09/11/2020 19:18:36	$ 1,080.00		$ 1,683.00
09/11/2020 16:31:36	13/11/2020 22:20:00	$ 1,080.00		$ 2,160.00
09/11/2020 17:23:07	13/11/2020 22:20:00	$ 393.00		$ 1,473.00
09/11/2020 19:18:59	09/11/2020 21:21:40	$ 1,140.00		$ 1,533.00
09/11/2020 20:10:09	10/11/2020 18:34:37	$ 386.00		$ 1,526.00
09/11/2020 21:22:40	10/11/2020 18:34:37	$ 196.00		$ 582.00
09/11/2020 21:26:51	09/11/2020 22:06:29	$ 692.00		$ 888.00
09/11/2020 22:06:53	10/11/2020 15:39:54	$ 326.00
09/11/2020 22:06:53	10/11/2020 16:34:18	$ 326.00		$ 652.00
09/11/2020 22:06:53	10/11/2020 20:38:47	$ 652.00		$ 978.00
10/11/2020 15:55:51	10/11/2020 16:05:53	$ 466.00		$ 1,118.00
10/11/2020 16:23:32	10/11/2020 20:25:31	$ 506.00
10/11/2020 16:55:04	10/11/2020 17:50:32	$ 465.00		$ 971.00
10/11/2020 18:33:33	10/11/2020 19:23:51	$ 354.00
10/11/2020 18:33:33	10/11/2020 20:17:18	$ 236.00		$ 590.00
10/11/2020 19:08:45	10/11/2020 20:17:18	$ 198.00		$ 434.00
10/11/2020 20:26:18	10/11/2020 21:58:23	$ 698.00
10/11/2020 21:28:39	10/11/2020 21:59:22	$ 435.00		$ 1,133.00
11/11/2020 15:33:25	11/11/2020 15:35:57	$ 337.33
11/11/2020 15:33:25	11/11/2020 16:33:06	$ 674.67		$ 1,012.00
11/11/2020 15:38:37	11/11/2020 19:16:19	$ 581.00		$ 1,255.67
11/11/2020 15:38:37	11/11/2020 21:58:40	$ 581.00		$ 1,162.00
11/11/2020 16:05:25	11/11/2020 20:44:39	$ 218.00		$ 799.00
11/11/2020 16:41:10	11/11/2020 21:02:51	$ 260.00		$ 478.00
11/11/2020 16:41:10	11/11/2020 21:41:45	$ 390.00		$ 650.00
11/11/2020 19:10:39	13/11/2020 21:06:00	$ 138.00		$ 528.00
11/11/2020 19:30:52	11/11/2020 21:06:12	$ 250.00		$ 388.00
11/11/2020 20:49:37	11/11/2020 21:56:33	$ 205.00		$ 455.00
11/11/2020 20:49:37	13/11/2020 22:20:00	$ 410.00		$ 615.00
11/11/2020 21:04:08	11/11/2020 21:59:00	$ 202.00		$ 612.00
11/11/2020 21:04:08	13/11/2020 22:20:00	$ 404.00		$ 606.00
11/11/2020 21:35:59	11/11/2020 21:49:42	$ 490.00		$ 894.00
11/11/2020 21:50:45	12/11/2020 16:02:36	$ 170.00
11/11/2020 21:50:45	12/11/2020 21:35:37	$ 340.00		$ 510.00
12/11/2020 15:33:50	12/11/2020 15:37:00	$ 315.00		$ 655.00
12/11/2020 15:33:50	12/11/2020 15:53:01	$ 630.00		$ 945.00
12/11/2020 15:36:44	12/11/2020 21:59:42	$ 211.00		$ 841.00
12/11/2020 15:36:44	13/11/2020 22:20:00	$ 633.00		$ 844.00
12/11/2020 15:42:13	12/11/2020 21:52:25	$ 580.00		$ 1,213.00
12/11/2020 15:45:22	12/11/2020 16:37:43	$ 395.00		$ 975.00
12/11/2020 15:45:22	13/11/2020 22:20:00	$ 395.00		$ 790.00
12/11/2020 16:20:41	12/11/2020 21:59:05	$ 695.00		$ 1,090.00
12/11/2020 16:49:34	12/11/2020 21:59:59	$ 520.00		$ 1,215.00
12/11/2020 17:58:55	13/11/2020 22:20:00	$ 395.00		$ 915.00
12/11/2020 19:21:55	13/11/2020 22:20:00	$ 405.00		$ 800.00
12/11/2020 19:33:31	12/11/2020 21:58:38	$ 540.00		$ 945.00
13/11/2020 15:30:10	13/11/2020 22:20:00	$ 618.00
13/11/2020 15:30:41	13/11/2020 22:20:00	$ 270.00		$ 888.00
13/11/2020 15:36:55	13/11/2020 22:20:00	$ 584.00		$ 854.00
13/11/2020 16:00:31	13/11/2020 22:20:00	$ 206.00		$ 790.00
13/11/2020 16:46:45	13/11/2020 19:04:58	$ 140.00		$ 346.00
13/11/2020 16:46:45	13/11/2020 19:26:11	$ 280.00		$ 420.00
13/11/2020 16:49:00	13/11/2020 21:31:43	$ 140.00		$ 420.00
13/11/2020 19:11:01	13/11/2020 21:35:51	$ 126.00		$ 266.00
13/11/2020 19:26:26	13/11/2020 21:25:09	$ 576.00		$ 702.00
16/11/2020 15:30:29	16/11/2020 15:38:03	$ 860.00
16/11/2020 15:31:06	16/11/2020 16:29:01	$ 1,124.00		$ 1,984.00
16/11/2020 15:35:58	17/11/2020 16:29:23	$ 1,013.00		$ 2,137.00
16/11/2020 15:44:13	17/11/2020 21:59:10	$ 580.00		$ 1,593.00
16/11/2020 16:30:07	17/11/2020 20:43:00	$ 650.00		$ 1,230.00
16/11/2020 16:39:57	16/11/2020 18:02:21	$ 410.33		$ 1,060.33
16/11/2020 16:39:57	16/11/2020 20:47:19	$ 410.33		$ 820.67
16/11/2020 16:39:57	16/11/2020 21:35:43	$ 410.33		$ 820.67
16/11/2020 19:42:21	16/11/2020 21:40:02	$ 232.00		$ 642.33
16/11/2020 19:42:21	16/11/2020 21:57:10	$ 464.00		$ 696.00
16/11/2020 20:48:43	16/11/2020 21:54:38	$ 435.00		$ 899.00
17/11/2020 15:30:53	17/11/2020 16:22:19	$ 777.00
17/11/2020 15:33:07	17/11/2020 21:51:57	$ 830.00		$ 1,607.00
17/11/2020 16:42:15	17/11/2020 20:55:09	$ 1,495.00		$ 2,325.00
17/11/2020 16:45:13	19/11/2020 20:25:16	$ 370.00		$ 1,865.00
17/11/2020 16:45:13	20/11/2020 15:45:08	$ 370.00		$ 740.00
17/11/2020 17:39:03	17/11/2020 20:35:17	$ 305.00		$ 675.00
17/11/2020 21:25:02	17/11/2020 22:02:40	$ 784.00
17/11/2020 21:53:46	20/11/2020 22:20:00	$ 582.00		$ 1,366.00
18/11/2020 15:32:51	20/11/2020 22:20:00	$ 1,250.00		$ 1,832.00
18/11/2020 15:41:43	18/11/2020 17:50:00	$ 242.00		$ 1,492.00
18/11/2020 15:41:43	18/11/2020 18:05:06	$ 242.00		$ 484.00
18/11/2020 16:03:06	18/11/2020 17:11:08	$ 586.00		$ 828.00
18/11/2020 16:49:57	20/11/2020 22:20:00	$ 354.00		$ 940.00
18/11/2020 17:30:23	18/11/2020 17:52:50	$ 452.00		$ 806.00
18/11/2020 17:50:36	18/11/2020 18:08:26	$ 192.00		$ 644.00
18/11/2020 17:50:36	18/11/2020 19:21:49	$ 192.00		$ 384.00
18/11/2020 17:50:36	18/11/2020 19:49:59	$ 192.00		$ 384.00
18/11/2020 18:26:19	18/11/2020 19:14:17	$ 840.00		$ 1,032.00

No, I'm trying to sum ALL instances where times overlap. The process would be:

1. Select row 2
2. For every row in the range find each instance where the start and end times overlap with those in A2 and B2
3. For each of those identified overlapping rows, sum the corresponding Column C values and put that total in cell D2
4. Select row 3 and repeat the above process until the final row

Thanks

 

[sandy666]

maybe

START TIME	END TIME	Cost
09/11/2020 15:31:24	09/11/2020 16:04:35	$ 1,240.00		$ 1,240.00
09/11/2020 15:31:24	09/11/2020 20:04:47	$ 620.00		$ 1,240.00
09/11/2020 15:36:33	09/11/2020 15:49:35	$ 1,012.00		$ 1,860.00
09/11/2020 15:36:33	09/11/2020 16:20:57	$ 253.00		$ 2,872.00
09/11/2020 15:36:33	13/11/2020 22:20:00	$ 253.00		$ 3,125.00
09/11/2020 15:58:15	13/11/2020 22:20:00	$ 785.00		$ 3,378.00
09/11/2020 16:05:11	13/11/2020 22:20:00	$ 603.00		$ 4,163.00
09/11/2020 16:29:43	09/11/2020 19:18:36	$ 1,080.00		$ 4,766.00
09/11/2020 16:31:36	13/11/2020 22:20:00	$ 1,080.00		$ 5,846.00
09/11/2020 17:23:07	13/11/2020 22:20:00	$ 393.00		$ 6,926.00
09/11/2020 19:18:59	09/11/2020 21:21:40	$ 1,140.00		$ 7,319.00
09/11/2020 20:10:09	10/11/2020 18:34:37	$ 386.00		$ 8,459.00
09/11/2020 21:22:40	10/11/2020 18:34:37	$ 196.00		$ 8,845.00
09/11/2020 21:26:51	09/11/2020 22:06:29	$ 692.00		$ 9,041.00
09/11/2020 22:06:53	10/11/2020 15:39:54	$ 326.00

or try to post expected result based on your example (no less than 20 rows)

 

[nicholaspars]

Thanks Jason

I tried the formula and have attached via XL2BB, but doesn't look quite right, for example the jump in row 6 from $4,726 to $28,700 in row 7 is not feasible.

Book1
	A	B	C	D
1	START TIME	END TIME	Cost	SUM OF OVERLAP
2	09/11/2020 15:31:24	09/11/2020 16:04:35	$1,240.00	$4,163.00
3	09/11/2020 15:31:24	09/11/2020 20:04:47	$620.00	$8,459.00
4	09/11/2020 15:36:33	09/11/2020 15:49:35	$1,012.00	$3,378.00
5	09/11/2020 15:36:33	09/11/2020 16:20:57	$253.00	$4,766.00
6	09/11/2020 15:36:33	13/11/2020 22:20:00	$253.00	$28,700.00
7	09/11/2020 15:58:15	13/11/2020 22:20:00	$785.00	$27,688.00
8	09/11/2020 16:05:11	13/11/2020 22:20:00	$603.00	$26,448.00
9	09/11/2020 16:29:43	09/11/2020 19:18:36	$1,080.00	$4,814.00
10	09/11/2020 16:31:36	13/11/2020 22:20:00	$1,080.00	$26,195.00
11	09/11/2020 17:23:07	13/11/2020 22:20:00	$393.00	$26,195.00
12	09/11/2020 19:18:59	09/11/2020 21:21:40	$1,140.00	$5,260.00
13	09/11/2020 20:10:09	10/11/2020 18:34:37	$386.00	$8,859.00
14	09/11/2020 21:22:40	10/11/2020 18:34:37	$196.00	$7,719.00
15	09/11/2020 21:26:51	09/11/2020 22:06:29	$692.00	$4,388.00
16	09/11/2020 22:06:53	10/11/2020 15:39:54	$326.00	$5,000.00
17	09/11/2020 22:06:53	10/11/2020 16:34:18	$326.00	$5,972.00
18	09/11/2020 22:06:53	10/11/2020 20:38:47	$652.00	$7,923.00
19	10/11/2020 15:55:51	10/11/2020 16:05:53	$466.00	$5,140.00
20	10/11/2020 16:23:32	10/11/2020 20:25:31	$506.00	$6,433.00
21	10/11/2020 16:55:04	10/11/2020 17:50:32	$465.00	$5,319.00
22	10/11/2020 18:33:33	10/11/2020 19:23:51	$354.00	$5,642.00
23	10/11/2020 18:33:33	10/11/2020 20:17:18	$236.00	$5,642.00
24	10/11/2020 19:08:45	10/11/2020 20:17:18	$198.00	$5,060.00
25	10/11/2020 20:26:18	10/11/2020 21:58:23	$698.00	$4,899.00
26	10/11/2020 21:28:39	10/11/2020 21:59:22	$435.00	$4,247.00
27	11/11/2020 15:33:25	11/11/2020 15:35:57	$337.33	$4,126.00
28	11/11/2020 15:33:25	11/11/2020 16:33:06	$674.67	$5,506.00
29	11/11/2020 15:38:37	11/11/2020 19:16:19	$581.00	$5,956.67
30	11/11/2020 15:38:37	11/11/2020 21:58:40	$581.00	$8,427.67
31	11/11/2020 16:05:25	11/11/2020 20:44:39	$218.00	$6,206.67
32	11/11/2020 16:41:10	11/11/2020 21:02:51	$260.00	$6,147.00
33	11/11/2020 16:41:10	11/11/2020 21:41:45	$390.00	$7,243.00
34	11/11/2020 19:10:39	13/11/2020 21:06:00	$138.00	$16,407.00
35	11/11/2020 19:30:52	11/11/2020 21:06:12	$250.00	$6,172.00
36	11/11/2020 20:49:37	11/11/2020 21:56:33	$205.00	$6,954.00
37	11/11/2020 20:49:37	13/11/2020 22:20:00	$410.00	$15,608.00
38	11/11/2020 21:04:08	11/11/2020 21:59:00	$202.00	$6,694.00
39	11/11/2020 21:04:08	13/11/2020 22:20:00	$404.00	$15,348.00
40	11/11/2020 21:35:59	11/11/2020 21:49:42	$490.00	$5,934.00
41	11/11/2020 21:50:45	12/11/2020 16:02:36	$170.00	$8,723.00
42	11/11/2020 21:50:45	12/11/2020 21:35:37	$340.00	$11,278.00
43	12/11/2020 15:33:50	12/11/2020 15:37:00	$315.00	$6,365.00
44	12/11/2020 15:33:50	12/11/2020 15:53:01	$630.00	$7,735.00
45	12/11/2020 15:36:44	12/11/2020 21:59:42	$211.00	$10,290.00
46	12/11/2020 15:36:44	13/11/2020 22:20:00	$633.00	$13,230.00
47	12/11/2020 15:42:13	12/11/2020 21:52:25	$580.00	$9,975.00
48	12/11/2020 15:45:22	12/11/2020 16:37:43	$395.00	$8,115.00
49	12/11/2020 15:45:22	13/11/2020 22:20:00	$395.00	$12,915.00
50	12/11/2020 16:20:41	12/11/2020 21:59:05	$695.00	$9,175.00
51	12/11/2020 16:49:34	12/11/2020 21:59:59	$520.00	$8,780.00
52	12/11/2020 17:58:55	13/11/2020 22:20:00	$395.00	$11,720.00
53	12/11/2020 19:21:55	13/11/2020 22:20:00	$405.00	$11,720.00
54	12/11/2020 19:33:31	12/11/2020 21:58:38	$540.00	$8,780.00
55	13/11/2020 15:30:10	13/11/2020 22:20:00	$618.00	$8,834.00
56	13/11/2020 15:30:41	13/11/2020 22:20:00	$270.00	$8,834.00
57	13/11/2020 15:36:55	13/11/2020 22:20:00	$584.00	$8,834.00
58	13/11/2020 16:00:31	13/11/2020 22:20:00	$206.00	$8,834.00
59	13/11/2020 16:46:45	13/11/2020 19:04:58	$140.00	$8,132.00
60	13/11/2020 16:46:45	13/11/2020 19:26:11	$280.00	$8,258.00
61	13/11/2020 16:49:00	13/11/2020 21:31:43	$140.00	$8,834.00
62	13/11/2020 19:11:01	13/11/2020 21:35:51	$126.00	$8,694.00
63	13/11/2020 19:26:26	13/11/2020 21:25:09	$576.00	$8,414.00
64	16/11/2020 15:30:29	16/11/2020 15:38:03	$860.00	$2,997.00
65	16/11/2020 15:31:06	16/11/2020 16:29:01	$1,124.00	$3,577.00
66	16/11/2020 15:35:58	17/11/2020 16:29:23	$1,013.00	$8,196.00
67	16/11/2020 15:44:13	17/11/2020 21:59:10	$580.00	$11,242.00
68	16/11/2020 16:30:07	17/11/2020 20:43:00	$650.00	$8,752.00
69	16/11/2020 16:39:57	16/11/2020 18:02:21	$410.33	$3,474.00
70	16/11/2020 16:39:57	16/11/2020 20:47:19	$410.33	$4,170.00
71	16/11/2020 16:39:57	16/11/2020 21:35:43	$410.33	$4,605.00
72	16/11/2020 19:42:21	16/11/2020 21:40:02	$232.00	$4,194.67
73	16/11/2020 19:42:21	16/11/2020 21:57:10	$464.00	$4,194.67
74	16/11/2020 20:48:43	16/11/2020 21:54:38	$435.00	$3,784.33
75	17/11/2020 15:30:53	17/11/2020 16:22:19	$777.00	$3,850.00
76	17/11/2020 15:33:07	17/11/2020 21:51:57	$830.00	$7,174.00
77	17/11/2020 16:42:15	17/11/2020 20:55:09	$1,495.00	$4,600.00
78	17/11/2020 16:45:13	19/11/2020 20:25:16	$370.00	$10,508.00
79	17/11/2020 16:45:13	20/11/2020 15:45:08	$370.00	$10,508.00
80	17/11/2020 17:39:03	17/11/2020 20:35:17	$305.00	$4,600.00
81	17/11/2020 21:25:02	17/11/2020 22:02:40	$784.00	$3,516.00
82	17/11/2020 21:53:46	20/11/2020 22:20:00	$582.00	$7,228.00
83	18/11/2020 15:32:51	20/11/2020 22:20:00	$1,250.00	$5,864.00
84	18/11/2020 15:41:43	18/11/2020 17:50:00	$242.00	$4,448.00
85	18/11/2020 15:41:43	18/11/2020 18:05:06	$242.00	$5,024.00
86	18/11/2020 16:03:06	18/11/2020 17:11:08	$586.00	$3,996.00
87	18/11/2020 16:49:57	20/11/2020 22:20:00	$354.00	$5,864.00
88	18/11/2020 17:30:23	18/11/2020 17:52:50	$452.00	$4,438.00
89	18/11/2020 17:50:36	18/11/2020 18:08:26	$192.00	$4,196.00
90	18/11/2020 17:50:36	18/11/2020 19:21:49	$192.00	$5,036.00
91	18/11/2020 17:50:36	18/11/2020 19:49:59	$192.00	$5,036.00
92	18/11/2020 18:26:19	18/11/2020 19:14:17	$840.00	$4,150.00
Sheet1

Cell Formulas
Range	Formula
D2:D92	D2	=SUMIFS([Cost],[START TIME],"<="&[@[END TIME]],[END TIME],">="&[@[START TIME]])

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D92	Expression	=AND($B2="",$A2<>"")	text	NO

Try

=SUMIFS(C:C,A:A,"<="&B2,B:B,">="&A2)

Not sure if that's right or not, I know in theory what I'm thinking but formulas can't be tested on screen captures (XL2BB is a much better way to post data samples, see link in my signature block below).

Jason

Having played around with this, your formula is in fact correctly identifying the total as requested. Many thanks for your assistance!

 

[jasonb75]

I think that the results appear incorrect due to a small number of rows overlapping a much wider time range than the rest. The result of 28700 is down to the times overlapping on more than 60 rows, while the 4866 above it overlaps less than 10.

From your later reply, it looks like you figured that out but I'll keep the thread on my watch list in case you find any anomalies.