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

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.




"Roger Govier" wrote:

Hi Dale

10 is being included in both formulae.
You need to decide where you want 10 to be included and adjust the
appropriate formula.
One should be <=10
The other 10
You can't have = in both formulae, otherwise you will get a double count.

--
Regards
Roger Govier

"Dale G" wrote in message
...
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

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" wrote:

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

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.


"Roger Govier" wrote:

Hi Dale

Using the 2 sets of data you posted, but placing the data and result on the
same sheet (just for convenience) I get the same results with both sets when
I use the following formula in B8 and copied down
=SUMPRODUCT(--(LEFT($F$3:$F$17,4)=$A8),--($E$3:$E$17<=--"0:10"),
--($E$3:$E$17<""))
Result 511n 6, 511s 7

In C8, if I use the formula
=SUMPRODUCT(--(LEFT($F$3:$F$17,4)=$A8),--($E$3:$E$17--"0:10"),--($E$3:$E$17<""))
I get all zeros. Correctly.
If I change the data in the last row to give a time of 00:10:01, then I get
counts of 6 and 6 in column B, and a count of 1 in column C against 511s

I didn't quite understand your posting about the data not giving the same
results.
You mentioned Cell E2.
If you had data in row 2, it would not get counted, as your formula is
starting from cell E3

As I mentioned in a previous posting, the last term in the second formula is
redundant. If the value is 10, it cannot be Null, and would never be
counted, whereas a Null cell would get counted in the <=10
The second formula only needs to be
=SUMPRODUCT(--(LEFT($F$3:$F$17,4)=$A8),--($E$3:$E$17--"0:10"))

In both cases, the formula needs adjusting to suit your ranges and sheet
names.
--
Regards
Roger Govier

"Dale G" wrote in message
...
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" wrote:

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

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

"Dale G" wrote:

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.


"Roger Govier" wrote:

Hi Dale

Using the 2 sets of data you posted, but placing the data and result on the
same sheet (just for convenience) I get the same results with both sets when
I use the following formula in B8 and copied down
=SUMPRODUCT(--(LEFT($F$3:$F$17,4)=$A8),--($E$3:$E$17<=--"0:10"),
--($E$3:$E$17<""))
Result 511n 6, 511s 7

In C8, if I use the formula
=SUMPRODUCT(--(LEFT($F$3:$F$17,4)=$A8),--($E$3:$E$17--"0:10"),--($E$3:$E$17<""))
I get all zeros. Correctly.
If I change the data in the last row to give a time of 00:10:01, then I get
counts of 6 and 6 in column B, and a count of 1 in column C against 511s

I didn't quite understand your posting about the data not giving the same
results.
You mentioned Cell E2.
If you had data in row 2, it would not get counted, as your formula is
starting from cell E3

As I mentioned in a previous posting, the last term in the second formula is
redundant. If the value is 10, it cannot be Null, and would never be
counted, whereas a Null cell would get counted in the <=10
The second formula only needs to be
=SUMPRODUCT(--(LEFT($F$3:$F$17,4)=$A8),--($E$3:$E$17--"0:10"))

In both cases, the formula needs adjusting to suit your ranges and sheet
names.
--
Regards
Roger Govier

"Dale G" wrote in message
...
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" wrote:

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