Tracking Sheet help.

[Dale G]

Sheet 3 of my workbook is a tracking sheet. In cell B8 (sheet 3) I enter the
amount of time's that I've made an entry of time on sheet 2. The times I
enter on sheet 2 are used to track vehicles that are passing a set location.
On sheet 2 I enter the times in column D. The vehicle's I count have numbers
to ID them in column F.
The numbers look like 401N1501, 401N1514, 401S1516, 401S1600. The part of
the number I need to associate with my count is the first four digits, 401N,
401S.
Is it possible to do this?

 

[M Kan]

Yep, use a sumproduct function to count using the left 4 digits as the
criteria. Here's a step-by-step. The formula would look something like:

SUMPRODUCT(--(LEFT($B$5:$B$10,4)=B14))

where B14 was the 4 digits you wanted to count by.

http://www.kan.org/tips/excel_sumproduct_advanced2.php

 

[Dale G]

Example of sheet 3

A B C D
401n
401s 1
402n
402s 3
404n
404s
405n
405s



Example
Run # Vehicle Time Actual Diff Schedule
924 0 4:48 535S0448
208 0 5:13 401S0459
904 0 5:18 535S0518
908 0 5:25 511S0518
706 0 5:36 401S0522
224 0 5:48 535S0548
708 0 5:49 401S0535


her are some examples. I'm not sure how to make that work. when I make an
entry in Sheet 2 column D, I need sheet 3 column B to count the entry,
according
to the numers in column A of sheet 3 and column F of sheet 2. 401N, 401S.

 

[M Kan]

I think:

SUMPRODUCT(--(LEFT(Sheet2!$F$5:$F$100,4)=Sheet3!A2),--(NOT(ISBLANK(Sheet2!$D$5:$D$100)))

I hope I got all of my parens right. This would count cells that have
entries and where the left 4 characters matches your tracking sheet

 

[Dale G]

That works very well, Thank You so much.
I wonder if this could go a step further. I use column C of sheet 3 for the
count, if the time I enter (on sheet 2, column D) = greater than 10+ minutes
difference in column E sheet 2 (Diff). Is this possible?


Example sheet 3

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



Example sheet 2

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 401S0459
904 0 5:18 535S0518
908 0 5:25 511S0518

Maybe the formulas can be set to column E from sheet 2 ?

 

[M Kan]

Not sure what the time format is, but yes. just add another condition that
evaluates the difference <= :10 for the first metric and modify this for the
second column such that the difference is >10

 

[Dale G]

That sounds good, but i'm not sure were to place those in the formula.

 

[Dale G]

Just as I thought, I don't know where to place <= :10 or >10. Could you
please assist me with the placement. I definitely need to take some classes

 

[M Kan]

I had a little difficulty with the time. One thing you can do is to enter
the time in a separate cell and then refer to it in the sumproduct.

 

[Dale G]

So far this works in column B.

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


And this for column C.

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


On sheet 3 column B This counts all the left nubers that match from sheet 2,
then if the difference is more then 10 minutes it subtracts 1 from that count
and places 1 in column C. I like the way it works, but it defeats the purpose
of (NOT(ISBLANK(.
Is there a way to keep the (NOT(ISBLANK in the formula. (Notice I've changed
the column that the entry info comes from). Also column E in sheet 2 is set
up to - the difference from D & C,(=D-C). So it has entry's of ### until the
enrty is made in D.

 

[Dale G]

Thank you for your reply. Between you, & M KAN, & Bob P, & others I came up
with something very close to your sugestion.
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192<=--"0:10"))
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192>=--"0:10"))
Thanks for your help. As you might have notice I'm still working on it.

 

[Dale G]

I guess I spoke to soon, there is some problem with using this formula. when
the difference = 10 it won't place the count in the right column. I tried to
fix it, but was not successful. Any sugesstion?

Example sheet 3 (OPT)


A B C

Route 0-10 +10
401n 0 0
401s 0 0
402n 0 0
402s 0 0
404n 0 0
535s 0 1



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

=IF(D3="","",D3-C3
=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<>""))