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?

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

--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Dale G" wrote:

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?

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

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

--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Dale G" wrote:

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?

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

--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Dale G" wrote:

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

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

--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Dale G" wrote:

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?

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

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

--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Dale G" wrote:

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

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

--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Dale G" wrote:

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?

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
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Dale G" wrote:

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

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

--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Dale G" wrote:

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

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

--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Dale G" wrote:

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?