| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
=SUMPRODUCT(--(LEFT Bob Phillips
I have a work book to keep track of route times.
Each sheet has a scheduled time, an actual time, and a calculated column for the difference. The route numbers are the same throughout the day, and they are separated by a run number. Im trying to do a summary on each route counted, and separate them by whether they are within 10min of the scheduled time or over 10min late. This is what I have so far but its not working. =SUMPRODUCT(--(LEFT('4th'!$F$3:$F$96,4)='4th'!G4),--(ABS('4th'!$C$3:$C$96-'4th'!$D$3:$D$96)*SIGN('4th'!$D$3:$D$96-'4th'!$C$3:$C$96)<=--"0:10:59")--('4th'!$D$3:$D$96<"")) A B C D E F G H I Run # Location Sched time Actual Diff Schedule Route # 0 -10 11+ 930 Hew/Virg 9:10 ### 510S0910 414n 970 Ash Way 9:11 ### 511S0911 414s 900 Sodo/Royal 9:15 ## 510N0915 510n 952 10th/102nd 9:15 ### 535N0915 510s 800 LTC 9:18 ### 535S0918 511n 912 Sodo/Royal 9:26 ### 511N0926 511s 246 Ash Way 9:26 ### 511S0926 535n 960 Hew/Virg 9:40 ### 510S0940 535s 958 Ash Way 9:41 ### 511S0941 904 Sodo/Royal 9:45 ### 510N0945 920 10th/102nd 9:45 ### 535N0945 908 LTC 9:48 ### 535S0948 938 McCull 9:50 ### 414S0950 936 Sodo/Royal 9:56 ### 511N0956 902 Hew/Virg 10:10 ### 510S1010 966 Ash Way 10:11 ### 511S1011 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
=SUMPRODUCT(--(LEFT Bob Phillips
Is this closer?
=SUMPRODUCT(--(LEFT($F$3:$F$96,4)=G4),--(ABS($D$3:$D$96-$C$3:$C$96)<=--"0:10:59")--($D$3:$D$96<"")) "Dale G" wrote: I have a work book to keep track of route times. Each sheet has a scheduled time, an actual time, and a calculated column for the difference. The route numbers are the same throughout the day, and they are separated by a run number. Im trying to do a summary on each route counted, and separate them by whether they are within 10min of the scheduled time or over 10min late. This is what I have so far but its not working. =SUMPRODUCT(--(LEFT('4th'!$F$3:$F$96,4)='4th'!G4),--(ABS('4th'!$C$3:$C$96-'4th'!$D$3:$D$96)*SIGN('4th'!$D$3:$D$96-'4th'!$C$3:$C$96)<=--"0:10:59")--('4th'!$D$3:$D$96<"")) A B C D E F G H I Run # Location Sched time Actual Diff Schedule Route # 0 -10 11+ 930 Hew/Virg 9:10 ### 510S0910 414n 970 Ash Way 9:11 ### 511S0911 414s 900 Sodo/Royal 9:15 ## 510N0915 510n 952 10th/102nd 9:15 ### 535N0915 510s 800 LTC 9:18 ### 535S0918 511n 912 Sodo/Royal 9:26 ### 511N0926 511s 246 Ash Way 9:26 ### 511S0926 535n 960 Hew/Virg 9:40 ### 510S0940 535s 958 Ash Way 9:41 ### 511S0941 904 Sodo/Royal 9:45 ### 510N0945 920 10th/102nd 9:45 ### 535N0945 908 LTC 9:48 ### 535S0948 938 McCull 9:50 ### 414S0950 936 Sodo/Royal 9:56 ### 511N0956 902 Hew/Virg 10:10 ### 510S1010 966 Ash Way 10:11 ### 511S1011 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
=SUMPRODUCT(--(LEFT Bob Phillips
In article ,
Dale wrote: Is this closer? =SUMPRODUCT(--(LEFT($F$3:$F$96,4)=G4),--(ABS($D$3:$D$96-$C$3:$C$96)<=--"0:10:5 9")--($D$3:$D$96<"")) It looks like you're missing a comma after the second argument... =SUMPRODUCT(--(LEFT($F$3:$F$96,4)=G4),--(ABS($D$3:$D$96-$C$3:$C$96)<="0:1 0:59"+0),--($D$3:$D$96<"")) -- Domenic http://www.xl-central.com |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
=SUMPRODUCT(--(LEFT Bob Phillips
Your right, I had that comma at one time but I lost it. Thank you.
within 10 minutes, =SUMPRODUCT(--(LEFT($F$3:$F$96,4)=G4),--(ABS($C$3:$C$96-$D$3:$D$96)*SIGN($D$3:$D$96-$C$3:$C$96)<=--"0:10:59"),--($D$3:$D$96<"")) Later then 10, =SUMPRODUCT(--(LEFT($F$3:$F$123,4)=G4),--(ABS($C$3:$C$123-$D$3:$D$123)*SIGN($D$3:$D$123-$C$3:$C$123)--"0:10:59"),--($D$3:$D$123<"")) "Domenic" wrote: In article , Dale wrote: Is this closer? =SUMPRODUCT(--(LEFT($F$3:$F$96,4)=G4),--(ABS($D$3:$D$96-$C$3:$C$96)<=--"0:10:5 9")--($D$3:$D$96<"")) It looks like you're missing a comma after the second argument... =SUMPRODUCT(--(LEFT($F$3:$F$96,4)=G4),--(ABS($D$3:$D$96-$C$3:$C$96)<="0:1 0:59"+0),--($D$3:$D$96<"")) -- Domenic http://www.xl-central.com |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
=SUMPRODUCT(--(LEFT Bob Phillips
In article ,
Dale wrote: Your right, I had that comma at one time but I lost it. Thank you. within 10 minutes, =SUMPRODUCT(--(LEFT($F$3:$F$96,4)=G4),--(ABS($C$3:$C$96-$D$3:$D$96)*SIGN($D$3: $D$96-$C$3:$C$96)<=--"0:10:59"),--($D$3:$D$96<"")) Later then 10, =SUMPRODUCT(--(LEFT($F$3:$F$123,4)=G4),--(ABS($C$3:$C$123-$D$3:$D$123)*SIGN($D $3:$D$123-$C$3:$C$123)--"0:10:59"),--($D$3:$D$123<"")) With regards to your first formula, if for example the scheduled time is 10:15 and the arrival time is 10:00 the second argument would be evaluated as true, even though the difference in time is greater than 10 minutes and 59 seconds. Any reason why the following would not suffice? =SUMPRODUCT(--(LEFT($F$3:$F$96,4)=G5),--(ABS($D$3:$D$96-$C$3:$C$96)<="0:1 0:59"+0),--($D$3:$D$96<"")) and =SUMPRODUCT(--(LEFT($F$3:$F$96,4)=G5),--(ABS($D$3:$D$96-$C$3:$C$96)"0:10 :59"+0),--($D$3:$D$96<"")) -- Domenic http://www.xl-central.com |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
=SUMPRODUCT(--(LEFT Bob Phillips
I ended up with this,
=SUMPRODUCT(--(LEFT($F$3:$F$96,4)=G4),--(ABS($C$3:$C$96-$D$3:$D$96)<=--"0:10:59"),--($D$3:$D$96<"")) But I see your point. In some situation an early arrival is acceptable, and counted in the 0-10 column. What I need is a formula that will count any early arrival in the same category as on time. So less than 0 will go in the column marked 0-10. I have been using the formula below, and it was on a second sheet. It works, but some reason it wont work here. On time or early, in column 0-10 =SUMPRODUCT(--(LEFT('LTC 12-15'!$F$3:$F$192,4)='OTP LTC 12-15'!A3),--(ABS('LTC 12-15'!$C$3:$C$192-'LTC 12-15'!$D$3:$D$192)*SIGN('LTC 12-15'!$D$3:$D$192-'LTC 12-15'!$C$3:$C$192)<=--"0:10:59"),--('LTC 12-15'!$D$3:$D$192<"")) Late, greater the 11 minutes in column 11+ =SUMPRODUCT(--(LEFT('LTC 12-15'!$F$3:$F$192,4)='OTP LTC 12-15'!A3),--(ABS('LTC 12-15'!$C$3:$C$192-'LTC 12-15'!$D$3:$D$192)*SIGN('LTC 12-15'!$D$3:$D$192-'LTC 12-15'!$C$3:$C$192)--"0:10:59"),--('LTC 12-15'!$D$3:$D$192<"")) Any additional help is appreciated. On this particular work sheet, the times are all departures, and not permitted to leave early. Still having and using the proper formula would be very good. "Domenic" wrote: In article , Dale wrote: Your right, I had that comma at one time but I lost it. Thank you. within 10 minutes, =SUMPRODUCT(--(LEFT($F$3:$F$96,4)=G4),--(ABS($C$3:$C$96-$D$3:$D$96)*SIGN($D$3: $D$96-$C$3:$C$96)<=--"0:10:59"),--($D$3:$D$96<"")) Later then 10, =SUMPRODUCT(--(LEFT($F$3:$F$123,4)=G4),--(ABS($C$3:$C$123-$D$3:$D$123)*SIGN($D $3:$D$123-$C$3:$C$123)--"0:10:59"),--($D$3:$D$123<"")) With regards to your first formula, if for example the scheduled time is 10:15 and the arrival time is 10:00 the second argument would be evaluated as true, even though the difference in time is greater than 10 minutes and 59 seconds. Any reason why the following would not suffice? =SUMPRODUCT(--(LEFT($F$3:$F$96,4)=G5),--(ABS($D$3:$D$96-$C$3:$C$96)<="0:1 0:59"+0),--($D$3:$D$96<"")) and =SUMPRODUCT(--(LEFT($F$3:$F$96,4)=G5),--(ABS($D$3:$D$96-$C$3:$C$96)"0:10 :59"+0),--($D$3:$D$96<"")) -- Domenic http://www.xl-central.com |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
=SUMPRODUCT(--(LEFT Bob Phillips
Ok I was wrong the one I have does work.
On time or early, =SUMPRODUCT(--(LEFT($F$3:$F$192,4)=G4),--(ABS($C$3:$C$192-$D$3:$D$192)*SIGN($D$3:$D$192-$C$3:$C$192)<=--"0:10:59"),--($D$3:$D$192<"")) Greater than 11. =SUMPRODUCT(--(LEFT($F$3:$F$192,4)=G4),--(ABS($C$3:$C$192-$D$3:$D$192)*SIGN($D$3:$D$192-$C$3:$C$192)--"0:10:59"),--($D$3:$D$192<"")) I think I got this from B Phillips. Thank for your help. Please let me know if you find another way. How do I mark a post as answered? "Dale G" wrote: I have a work book to keep track of route times. Each sheet has a scheduled time, an actual time, and a calculated column for the difference. The route numbers are the same throughout the day, and they are separated by a run number. Im trying to do a summary on each route counted, and separate them by whether they are within 10min of the scheduled time or over 10min late. This is what I have so far but its not working. =SUMPRODUCT(--(LEFT('4th'!$F$3:$F$96,4)='4th'!G4),--(ABS('4th'!$C$3:$C$96-'4th'!$D$3:$D$96)*SIGN('4th'!$D$3:$D$96-'4th'!$C$3:$C$96)<=--"0:10:59")--('4th'!$D$3:$D$96<"")) A B C D E F G H I Run # Location Sched time Actual Diff Schedule Route # 0 -10 11+ 930 Hew/Virg 9:10 ### 510S0910 414n 970 Ash Way 9:11 ### 511S0911 414s 900 Sodo/Royal 9:15 ## 510N0915 510n 952 10th/102nd 9:15 ### 535N0915 510s 800 LTC 9:18 ### 535S0918 511n 912 Sodo/Royal 9:26 ### 511N0926 511s 246 Ash Way 9:26 ### 511S0926 535n 960 Hew/Virg 9:40 ### 510S0940 535s 958 Ash Way 9:41 ### 511S0941 904 Sodo/Royal 9:45 ### 510N0945 920 10th/102nd 9:45 ### 535N0945 908 LTC 9:48 ### 535S0948 938 McCull 9:50 ### 414S0950 936 Sodo/Royal 9:56 ### 511N0956 902 Hew/Virg 10:10 ### 510S1010 966 Ash Way 10:11 ### 511S1011 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT with LEFT() criteria? | Excel Worksheet Functions | |||
| sumif or sumproduct with a left statement | Excel Discussion (Misc queries) | |||
| SUMPRODUCT AND LEFT | Excel Worksheet Functions | |||
| SUMPRODUCT - Bob Phillips | Excel Discussion (Misc queries) | |||
| Left and Sumproduct | Excel Discussion (Misc queries) | |||
![Dale G[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
