Seeking help to sum up certain data given conditions

abberyfarm

Well-known Member
Joined
Aug 14, 2011
Messages
733
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
ABCDEFGH
1start_datestart_timeend_dateend_timeTypeTrip distanceNew Calc.
207/01/201211:21:1907/01/201211:24:51T0.87
307/01/201211:29:5407/01/201211:33:16T0.8934.84=sum(F5:F12)
407/01/201211:33:5807/01/201212:28:03C
507/01/201214:53:3807/01/201215:19:09T9.9124.93=sum(F6:F12)
607/01/201215:29:0307/01/201215:38:49T2.4822.45=sum(F7:F12)
707/01/201217:18:2707/01/201217:37:22T7.9914.46=sum(F8:F12)
807/01/201217:39:5607/01/201217:49:47T3.311.16=sum(F9:F12)
907/01/201217:50:3707/01/201217:55:18T1.369.8=sum(F10:F12)
1007/01/201218:36:4707/01/201218:45:07T0.479.33=sum(F11:F12)
1107/01/201218:46:4207/01/201218:54:47T3.16.23=sum(F12:F12)
1207/01/201218:57:4407/01/201219:13:18T6.238.01=sum(F14:F15)
1307/01/201219:17:0607/01/201220:44:43C
1407/01/201220:44:4307/01/201220:58:04T4.223.79=sum(F15:F15)
1507/01/201223:02:4507/01/201223:12:28T3.790Next "T" is the next date
1607/01/201223:14:4108/01/201202:18:45C
1708/01/201210:45:5108/01/201210:49:51T0.880.88=sum(F18:F18)
1808/01/201210:53:1608/01/201210:56:30T0.8853.14=sum(F20:F24)
1908/01/201210:57:5008/01/201212:17:39C
2008/01/201213:26:3008/01/201214:19:36T22.98=sum(F21:F24)
2108/01/201215:26:0008/01/201215:42:26T8.07=sum(F22:F24)
2208/01/201216:36:5608/01/201217:16:32T18=sum(F23:F24)
2308/01/201217:17:2308/01/201217:23:37T2.6=sum(F24:F24)
2408/01/201217:31:1508/01/201217:36:06T1.49
2508/01/201217:38:5308/01/201218:19:23C
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


 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

hurgadion

Active Member
Joined
Mar 19, 2010
Messages
426
Hi,
if I understood Your problem enough the below array Formula (Ctrl+Shift+Enter) should be useful:
Code:
=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.
 
Last edited:

hurgadion

Active Member
Joined
Mar 19, 2010
Messages
426
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

Well-known Member
Joined
Aug 14, 2011
Messages
733
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
ABCDEFGH
1start_datestart_timeend_dateend_timeTypeTrip distanceCalc.Actual values
207/01/201211:21:1907/01/201211:24:51T0.87**
307/01/201211:29:5407/01/201211:33:16T0.8934.8434.84
407/01/201211:33:5807/01/201212:28:03C***
507/01/201214:53:3807/01/201215:19:09T9.9124.9324.93
607/01/201215:29:0307/01/201215:38:49T2.4822.4522.45
707/01/201217:18:2707/01/201217:37:22T7.9914.4614.46
807/01/201217:39:5607/01/201217:49:47T3.311.1611.16
907/01/201217:50:3707/01/201217:55:18T1.369.89.8
1007/01/201218:36:4707/01/201218:45:07T0.479.339.33
1107/01/201218:46:4207/01/201218:54:47T3.16.236.23
1207/01/201218:57:4407/01/201219:13:18T6.238.018.01
1307/01/201219:17:0607/01/201220:44:43C***
1407/01/201220:44:4307/01/201220:58:04T4.223.793.79
1507/01/201223:02:4507/01/201223:12:28T3.791.760
1607/01/201223:14:4108/01/201202:18:45C***
1708/01/201210:45:5108/01/201210:49:51T0.880.880.88
1808/01/201210:53:1608/01/201210:56:30T0.8853.1453.14
1908/01/201210:57:5008/01/201212:17:39C***
2008/01/201213:26:3008/01/201214:19:36T22.9830.16*
Sheet1
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,550
Members
414,316
Latest member
ExcelLee

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
Top