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