|
Tracking Sheet help.
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? |
Tracking Sheet help.
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? |
Tracking Sheet help.
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? |
Tracking Sheet help.
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? |
Tracking Sheet help.
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? |
Tracking Sheet help.
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? |
Tracking Sheet help.
That sounds good, but i'm not sure were to place those in the formula.
"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? |
Tracking Sheet help.
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? |
Tracking Sheet help.
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. -- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "Dale G" wrote: 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? |
Tracking Sheet help.
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. "M Kan" wrote: 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. -- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "Dale G" wrote: 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? |
Tracking Sheet help.
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? |
Tracking Sheet help.
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<"")) "Dale G" wrote: 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? |
| All times are GMT +1. The time now is 06:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com