Seeking help to sum up certain data given conditions

[abberyfarm]

Hi there,

Would anybody be able to help me out with a macro or formula to add up certain data?

I have data like this:

Excel Workbook
	A	B	C	D	E	F	G	H
1	start_date	start_time	end_date	end_time	Type	Trip distance	New Calc.
2	07/01/2012	11:21:19	07/01/2012	11:24:51	T	0.87
3	07/01/2012	11:29:54	07/01/2012	11:33:16	T	0.89	34.84	=sum(F5:F12)
4	07/01/2012	11:33:58	07/01/2012	12:28:03	C
5	07/01/2012	14:53:38	07/01/2012	15:19:09	T	9.91	24.93	=sum(F6:F12)
6	07/01/2012	15:29:03	07/01/2012	15:38:49	T	2.48	22.45	=sum(F7:F12)
7	07/01/2012	17:18:27	07/01/2012	17:37:22	T	7.99	14.46	=sum(F8:F12)
8	07/01/2012	17:39:56	07/01/2012	17:49:47	T	3.3	11.16	=sum(F9:F12)
9	07/01/2012	17:50:37	07/01/2012	17:55:18	T	1.36	9.8	=sum(F10:F12)
10	07/01/2012	18:36:47	07/01/2012	18:45:07	T	0.47	9.33	=sum(F11:F12)
11	07/01/2012	18:46:42	07/01/2012	18:54:47	T	3.1	6.23	=sum(F12:F12)
12	07/01/2012	18:57:44	07/01/2012	19:13:18	T	6.23	8.01	=sum(F14:F15)
13	07/01/2012	19:17:06	07/01/2012	20:44:43	C
14	07/01/2012	20:44:43	07/01/2012	20:58:04	T	4.22	3.79	=sum(F15:F15)
15	07/01/2012	23:02:45	07/01/2012	23:12:28	T	3.79	0	Next "T" is the next date
16	07/01/2012	23:14:41	08/01/2012	02:18:45	C
17	08/01/2012	10:45:51	08/01/2012	10:49:51	T	0.88	0.88	=sum(F18:F18)
18	08/01/2012	10:53:16	08/01/2012	10:56:30	T	0.88	53.14	=sum(F20:F24)
19	08/01/2012	10:57:50	08/01/2012	12:17:39	C
20	08/01/2012	13:26:30	08/01/2012	14:19:36	T	22.98		=sum(F21:F24)
21	08/01/2012	15:26:00	08/01/2012	15:42:26	T	8.07		=sum(F22:F24)
22	08/01/2012	16:36:56	08/01/2012	17:16:32	T	18		=sum(F23:F24)
23	08/01/2012	17:17:23	08/01/2012	17:23:37	T	2.6		=sum(F24:F24)
24	08/01/2012	17:31:15	08/01/2012	17:36:06	T	1.49
25	08/01/2012	17:38:53	08/01/2012	18:19:23	C
Sheet1

Would it be possible to sum up for each "T" event (Col E), the remaining distance still to travel on the same day (date) and/or before the next "C" event if another one occurs on the same day.

Appreciate any help

Thanks

John

 

[hurgadion]

Hi,
if I understood Your problem enough the below array Formula (Ctrl+Shift+Enter) should be useful:

=IF(E3="C","",SUM(INDIRECT("F"&IF(E3="T",MIN(IF(E4:E25="T",ROW(E4:E25),"")),"")&":F"&MIN(IF(IF(E4="C",E5:$E$25,E4:$E$25)="C",ROW(IF(E4="C",E5:$E$25,E4:$E$25));""))-1)))

Best regards.

 

[abberyfarm]

Hello,

Thank you for your reply.

It says that the formula contains an error?

Here is a sample workbook if you would like to try it? - you could try it in Column G
https://dl.dropbox.com/u/54057365/All/forum.xlsx

The actual values are in Column I.

Thanks for the help

John

 

[hurgadion]

abbeyfarm: Try to change the character ";" in the above Formula on the character ",". This is my local mistake... It should be enough to run the Formula for Your Data, best regards.

 

[abberyfarm]

Hello Hurgadion,

Thank you for taking the time to understand the problem.

The formula works very well. There is just one thing. You see the red cell should be "0" because the date changes in Column A.

Would it be possible to incorporate this into the formula?

Thank you

John

Excel Workbook
	A	B	C	D	E	F	G	H
1	start_date	start_time	end_date	end_time	Type	Trip distance	Calc.	Actual values
2	07/01/2012	11:21:19	07/01/2012	11:24:51	T	0.87	*	*
3	07/01/2012	11:29:54	07/01/2012	11:33:16	T	0.89	34.84	34.84
4	07/01/2012	11:33:58	07/01/2012	12:28:03	C	*	*	*
5	07/01/2012	14:53:38	07/01/2012	15:19:09	T	9.91	24.93	24.93
6	07/01/2012	15:29:03	07/01/2012	15:38:49	T	2.48	22.45	22.45
7	07/01/2012	17:18:27	07/01/2012	17:37:22	T	7.99	14.46	14.46
8	07/01/2012	17:39:56	07/01/2012	17:49:47	T	3.3	11.16	11.16
9	07/01/2012	17:50:37	07/01/2012	17:55:18	T	1.36	9.8	9.8
10	07/01/2012	18:36:47	07/01/2012	18:45:07	T	0.47	9.33	9.33
11	07/01/2012	18:46:42	07/01/2012	18:54:47	T	3.1	6.23	6.23
12	07/01/2012	18:57:44	07/01/2012	19:13:18	T	6.23	8.01	8.01
13	07/01/2012	19:17:06	07/01/2012	20:44:43	C	*	*	*
14	07/01/2012	20:44:43	07/01/2012	20:58:04	T	4.22	3.79	3.79
15	07/01/2012	23:02:45	07/01/2012	23:12:28	T	3.79	1.76	0
16	07/01/2012	23:14:41	08/01/2012	02:18:45	C	*	*	*
17	08/01/2012	10:45:51	08/01/2012	10:49:51	T	0.88	0.88	0.88
18	08/01/2012	10:53:16	08/01/2012	10:56:30	T	0.88	53.14	53.14
19	08/01/2012	10:57:50	08/01/2012	12:17:39	C	*	*	*
20	08/01/2012	13:26:30	08/01/2012	14:19:36	T	22.98	30.16	*
Sheet1

 

[abberyfarm]

Hi,

I figured this out, thank you!

Regards

John