=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

=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

=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

=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

=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

=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

=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