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.
"Roger Govier" wrote:
Hi Dale
Try
SUMPRODUCT(--(LEFT(Sheet2!$F$5:$F$100,4)=Sheet3!A2),--(Sheet2!$D$5:$D$100TIME(0,10,0))
TIME specifies (hours,minutes,seconds)
--
Regards
Roger Govier
"Dale G" wrote in message
...
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
on Excel, I love how this stuff works.
"M Kan" wrote:
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?