Problem with SUMPORDUCT formula

[Dale G]

I have more info in my other post (Tracking sheet help) & (sumproduct
function, Bob P please see tracking sheet help).

I'm using this in Sheet 2 (LTC)
=IF(D3="","",D3-C3)

and this in sheet 3 (OTP)

=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192<=--"0:10"),--(LTC!$E$3:$E$192<>""))
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192>=--"0:10"),--(LTC!$E$3:$E$192<>""))

I"m counting vehicles that pass a location. Sheet 2 (LTC) column C is the
time they are scheduled to pass. Column D is the time I enter that they
actually pass.

Column E sheet 2 is set to auto calculate the difference, =IF(D3="","",D3-C3)

Sheet 3 is used for ON TIME PERFORMANCE (OTP). If they pass from within 0 to
10 minutes I count them in column B of sheet 3 (OTP), and if they pass 11 or
more minutes late I count them in column C of sheet 3 (OTP).

The left 4 digits in column F Sheet 2 (LTC) are the same as column A sheet 3
(OTP)

My problem is the formula is not placing the count in the right column if
the difference = 10 & and if I change the formula to 11 the same occurs.

Would there be a way to have Sheet 3 set up to do the math. Similar to the
formula on sheet 2. =IF(D3="","",D3-C3) and if the total = under 0 to 10
minutes it will be entered in Sheet 3 column B and if the total was 11
minutes or more the count would be entered in sheet 3 column C?

(I said Under because sometimes they are allowed to pass before there
scheduled time so I end up with a negative that I have to manually enter (3-)
or (4-),(5-) in column E sheet 2, which further complicates the sheet, I
don't think there is a way to count or show negative time).

Hope this makes some sort of sense.



Example sheet 2(LTC)

A B C D E
F


Run Vehicle Time Actual Diff Schedule
924 0 4:48 4:59 0:11 535S0448
208 0 5:13 5:23 0:10 401S0459
904 0 5:18 5:28 0:10 535S0518
908 0 5:25 511S0518

Example sheet 3 (OPT)


A B C

Route 0-10 +10
401n 0 0
401s 0 0
402n 0 0
402s 0 0
511n 0 0
511s 0 0
535n 0 0
535s 0 0

 

[Dale G]

I tried your suggestion and still have the same problem. It does work most of
the time but there are some cells it won't work with 10. 9 is ok sometimes 11
doesn't work. I could e-mail a sample if that would help. there must be
something else I'm not doing right.



Sheet LTC
924 0 4:48 535S0448
208 0 5:13 5:23 0:10 401S0459
904 0 5:18 535S0518

Sheet OTP
Route 0-10 +10
401n 0 0
401s 0 0
402n 0 0
402s 0 0
404n
404s

in the example above is one of the cells that won't work if the difference
is 0:10.

 

[Dale G]

I discovered that this formula doesn't want to repeat the same calculation
back to back. Even using just the one that counts if<=--"0:10". And if I end
up with 10 minutes difference to start, in this case (Cell E 2) it won't
count the next one that is 10 minutes difference, (Cell E 3). Of course you
could test this to verify,

Run # Vehicle # Time Actual Diff Schedule

28 0 6:18 6:28 0:10 511S0610
20 0 6:37 6:43 0:06 511N0605
32 0 6:48 6:58 0:10 511S0640
26 0 7:07 7:10 0:03 511N0635
20 0 7:18 7:20 0:02 511S0710
28 0 7:37 7:45 0:08 511N0705
26 0 7:48 7:49 0:01 511S0740
32 0 8:07 8:11 0:04 511N0735
28 0 8:18 8:23 0:05 511S0810
62 0 8:18 8:24 0:06 535S0818
20 0 8:37 8:43 0:06 511N0805
32 0 8:48 8:55 0:07 511S0840
64 0 9:05 9:08 0:03 535N0821
26 0 9:07 9:10 0:03 511N0835
20 0 9:18 9:28 0:10 511S0910

And the results from this are,

Route 0-10 +10
510n
510s
511n 6
511s 6
513n
513s
532n 0
532s 0

What do you think? If I start the first entry like below, here are the
results.

28 0 6:18 6:20 0:02 511S0610
20 0 6:37 6:40 0:03 511N0605
32 0 6:48 6:53 0:05 511S0640
26 0 7:07 7:10 0:03 511N0635
20 0 7:18 7:23 0:05 511S0710
28 0 7:37 7:45 0:08 511N0705
26 0 7:48 7:52 0:04 511S0740
32 0 8:07 8:10 0:03 511N0735
28 0 8:18 8:19 0:01 511S0810
62 0 8:18 8:20 0:02 535S0818
20 0 8:37 8:43 0:06 511N0805
32 0 8:48 8:55 0:07 511S0840
64 0 9:05 9:15 0:10 535N0821
26 0 9:07 9:17 0:10 511N0835
20 0 9:18 9:28 0:10 511S0910


Route 0-10 +10

510n
510s
511n 6
511s 7
513n
513s
532n 0
532s 0

This is correct, and if I go to the top and and change the entry after going
this far down the list it will count correctly.

and this is only the one fumula <=--"0:10"

Is this something that can be solved?

 

[Dale G]

Ok, That's it, I changed the time to <=--"0:10:59 in B and >--"0:11" in C.
Thank you once again. (The example with E2 was set to E2 but I just copy and
pasted the the fomula from a different post). Thanks for your patient I
deffnitely have a lot to learn.

 

[Dale G]

Ok, not >--"0:11" in C, But >--"0:10" now were good.