Problem with SUMPORDUCT formula

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
0
DaleG (36)
8/7/2008 5:05:00 PM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
601 Views

Similar Articles

[PageSpeed] 34

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" <DaleG@discussions.microsoft.com> wrote in message 
> news:8AE21FB0-163D-490E-9D64-52DABAB2E025@microsoft.com...
> > 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 
> 
0
DaleG (36)
8/8/2008 10:16:01 PM
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
0
DaleG (36)
8/9/2008 1:46:00 AM
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" <DaleG@discussions.microsoft.com> wrote in message 
> news:1B036947-B009-4081-9BE8-D8E6A8777BBD@microsoft.com...
> > 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 
> 
0
DaleG (36)
8/9/2008 1:14:01 PM
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" <DaleG@discussions.microsoft.com> wrote in message 
> > news:1B036947-B009-4081-9BE8-D8E6A8777BBD@microsoft.com...
> > > 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 
> > 
0
DaleG (36)
8/9/2008 2:08:00 PM
Reply:

Similar Artilces:

copying a formula down wards
=INDEX(Proj_code,MATCH(AQ$6,F$7:F$198,0)) the above formula works in that it shows the first match. As there could be mulitpule records what do I need to do to so when I copy it down it will bring the next match or 0 if no further matches in the table it is searching? Many thanks UKMAN1 Hi, Try this ARRAY formula. ARRAY enter it and it will return the first match, drag down for the second etc. It will return an error if there isn't a second match so you could wrap the whole thing =isserror(formula etc =OFFSET(IF(ROWS(B$7:B7)<=COUNTIF(Proj_code,$E$1),INDEX($F...

windows search problem....
Hello! I have strange problem with windows search engine... i have 100000 txt files and im looking for special phrase in few txt files i know that phrase exist but windows search give me no information - can`t find. But on the other computer on the same phrase and same files works good. I have reinstall windows search ... but problem still exist... how can i resolve it? regards - HE ...

Sorting a column by using formula #3
I am trying to use sort function just to delete blank cells in between Sort order doesn't matter actually. Data is coming by the use of simple cell reference of "another sheet -- Prais ----------------------------------------------------------------------- Praise's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1558 View this thread: http://www.excelforum.com/showthread.php?threadid=27144 Hi you may use the following addin to filter out blank cells: http://www.rondebruin.nl/easyfilter.htm -- Regards Frank Kabel Frankfurt, Germany Praise wrote: &g...

Formulae in headers or footers
Is it possible to put totals in a header or footer? I haven't seen any place that says you can or can't. If you can, then how? Thanks, -- Lee Coleman You need VBA code to do that. If your data is in D1:D100, this will do as an example: Right-click the Excel LOGO near the file menu, select View Code, paste this in: Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.RightFooter = "Total = " & Application.Sum(Range("D1:D100")) End Sub Bob Umlas Excel MVP "Lee" wrote: > Is it possible to put totals in a header or f...

use formula on external data range
Hi All, I've got a problem with data I read from a database. I wrote a VBA Sub in Excel 2007 with retrieves data via ActiveSheet.QueryTables.Add() per OLEDB out of a database. This works fine and puts the data in the range beginning on cell "A8" Now my problem: I have a formula '=SUM(A8:A10) in cell "B1". This formula always comes out = 0, although there are values in the cells. But if I edit cell "A8" without changing the value (just the alignment changes) it is recognized and the formula shows = 1 What am I missing here ? Thanks ...

Using Invoice template: overriding formulae
I am having a problem when using the Excel 2000 Invoice template. Sometimes I enter text into the 'Quantity' column. Then Excel is unable to calculate the Total, in the blue shaded area. My question is: How do I override the formula calculation in the shaded area to simply enter the numbers and/or text of my choosing? WTIA Andrew Stucken -- Evil flourishes when good men do nothing. Don'y know how to override the original protectiion. Easiest would be to copy and paste into new sheet and use this to create your own template. Roger "Enigmaman" wrote: > I a...

Problem with DNS TTL on exchange and DC records
I am having a lot of problems with Exchange hanging on users and have narrowed it down to the DNS TTL's being too low. On the e2k3 box I have deselected the DNS auto register feature. I change the A record on the DC and bump the TTL up to 7 days. But it keeps setting back to 20 mins which is way too low. I would also like to set the DC's to 1 day, but I am having the same problem. What am I missing? On 8 Aug 2006 09:35:30 -0700, nate.sellers@gmail.com wrote: >I am having a lot of problems with Exchange hanging on users and have >narrowed it down to the DNS TTL's being...

Problem with Email Addresses in Our Domain
Exchange Server 2003. We just moved our entire organization's email from an outside server to our own Exchange Server. We used to be able to send email to recipients of the form "first.last@goodnewsjail.org" or "first.last@mail.goodnewsjail.org." Now when folks send to @mail.goodnewsjail.org, their messages get stuck forever in the "mail.goodnewsjail.org" queue. The queue never empties. What do I need to do so that people can use either @goodnewsjail.org or @mail.goodnewsjail.org on their recipient addresses? Thanks for the help. -- Dr. Doug Prui...

There is a problem communicating with the Microsoft CRM server
Hi, During the installation of the CRM Outlook Client I have a problem where I cannot connect to the CRM server. I used the same address to connect CRM Server via the web browser. I then proceeded with the installation, hoping I could configure this after I've completed the installation. I couldn't find anywhere how I could configure the CRM Outlook client (Bit daft I thought or is it just me??) When I fired up Outlook I got these messages: "There is a problem communicating with the Microsoft CRM server. The server might be unavailable. Try again later. If the problem...

Problem with Exchange 2003 Permissions
I have a 2003 installation which only has one mailbox database and one public folder database in the first storage group. the issue im having is that i cannot mount the public folders db with the error " The store could not be mounted because the active directory information was not replicated yet you cant either... press cancel or retry" and when I do a retry i got "The Microsoft exchange information store service could not find the specified object." ID no: c1041722 I googled a lot about this issue which is supposed to be a permissions issue but i had alre...

Technical problems with the web enabled newsgroup?
Hi, Is anyone else having trouble accessing this Great Plains newsgroup through the web browser interface? When I click on Great plains, sometimes it just hangs. Sometimes Great Plains comes up, but doing a search will cause it to hang. Logging into Passport before going to the web site doesn't seem to make any improvement. I have had go back to using Outlook Express. Any ideas? Regards, Brcobrem ...

C# Keyboard Hook Problems
Hi, I'm trying to implement a keyboard hook that will receive all keyboard events within my application, and only my application, using C# .NET 3.5 and VS 2008. I'm using the following code: private delegate IntPtr KeyboardHookCallback(int nCode, IntPtr wParam, IntPtr lParam); private static KeyboardHookCallback _callback = new KeyboardHookCallback(HookCallback); private const int WH_KEYBOARD_LL = 13; private static IntPtr SetHook() { return SetWindowsHookEx(WH_KEYBOARD_LL, _callback, IntPtr.Zero, Thread.CurrentThread.ManagedThreadId); } [DllImport("...

Connection problem between publisher and outlook (2003)
I've created a newsletter in publisher. I've clicked on send e-mail, then send this page as message. I get a messgae saying 'Publisher could not start the email program. Check network connection, etc' Outlokk is my email program and is up and running, there are no internet problems and other programs work fiine. I've tried detect and repair and also reinstalled office. Any other suggestions? -- Thanks Just checking - which version of Publisher? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "exmt...

outlook log in problem
i have installed office pro 2003 and defaulted my mail to outlook from outlook express. all data for mail accounts has been transfered like for like but when trying to send/recieve mail a log on pop-up requesting verification of user name and password appears. it then rejects these as unrecognised. all mail that has passed through o/express is showing. i ahve double checked my settings also deleted the settings and reinstalled them and deleted all settings at o/express and reinstalled them to no avail. and if i go back to o/express everything works fine??? loofer wrote: > i have in...

Resolving "Bounce" Problem
My customer has been placed on SpamCop's list for a second time due to "Excessive Bounces". I am already diverting bad e-mail to the Exchange 2003 BADMAIL folder. And, I continue to get a variety of 5.x.x event entries. While our DNS is a little complicated (e-mail is initially directed to TM's VirusWall and then forwarded to Exchange 2003), I don't see any issues with our DNS entries. Why are we getting "Excessive Bounces"? -- Gary Ainsworth 713-854-6693 On Tue, 17 Jan 2006 15:56:02 -0800, GDA <GDA@discussions.microsoft.com> wrote: >My cust...

text formatting problems
I receive spreadsheets from remote site support staff containing Ethernet addresses in a column. Often these are entered with spaces, hyphens, etc. between each pair. However, I need them to be 12 character strings, no spaces or other chars. The column is formatted as text, and I do a replace on the problem character. This works unless the address could be interpreted as an exponent; i.e 00-50-04-82-1E-20 becomes 5.00E+26 when I replace the hyphens with nothing. In addresses that do not contain alpha chars, the leading zeros are dropped. Has anyone else seen this? How do you get aroun...

2003 pop up "encountered a problem"
Pop up "Microsoft Outlook has encountered a problem and needs to close" appears often. Outlook then must be closed and start again. What is this and how can I correct it. Charles Kerr ...

Formula to Get the Max of a column and its definition
Let's say I have two columns... From: Sheet1 Column Z = Product Name Column B = Reject Quantity Problem: Using Sheet2, How can I have the Max value of the Column (from Sheet1) and have its Product Name from Column Z? Remember, th Sheet1 table is modified every now and then so the max value of B migh be different in every minute. Desired End Result: In Sheet2: Reject Quantity Product Name 10 Dent Please help me on this.... Otix -- Shadow_Otix ----------------------------------------------------------------------- Shadow_Otixz's Pr...

Outlook 2003 Synchronization Problems
Our company just upgraded our email server from exchange 2003 to the exchange 2007 a couple of weeks ago. Everything was Ok until yesterday. I am having a problem with only two computers using outlook 2003. The problem happens when we are inside our own LAN. There are two Synchronization errors I am getting and they are: First error: 9:18:41 Synchronizer Version 11.0.8000 9:18:41 Synchronizing Mailbox 'User Name' 9:18:41 Synchronizing Hierarchy 9:18:41 Done 9:18:41 Microsoft Exchange offline address book 9:18:41 0X8004010F Second Error: Task 'Microsoft E...

No Edge Server No Problem?
We are using an Ironmail appliance as a smarthost attached to our mail server that is functioning as all roles except the edge server. I'm trying to route mail out through the ironmail appliance but it does not seem to be working. There should not an issue routing mail out through the Ironmail appliance instead of an Edge server is there? Thanks. Nope, Edge isn't required. You might want to detail what is happening, what errors, can you manually telnet to the Ironmail appliance, etc, etc. -- Neil Hobson Exchange MVP http://www.msexchange.org/Neil_Hobson/ "D303M"...

GP 9 BR, problem setting up named printer
Perhaps I'm not doing something correctly...what I want to do is allow the users to select whatever printer they want. Some of them use local printers for checks, but I need to allow them to print reports to one or more of the network copier/printers. It appears I have to set a printer for System and Company before I can set anything else to Manual. If I do this for anyone who isn't an administrator, when they attempt to log in to GP (even on my PC as well as theirs, on which I tested a users' login), they receive "You don't have security privileges to open this w...

Help to modify a formula
This is my formula I need help with, in cell I10 this is entered =IF((I7-I6)*24>4,(I7-I6)*24-0.5,(I7-I6)*24) Problem is when cell I6 & I7 are blank cell I10 shows #value I need it to be blank or 0 any ideas? thanks all. Bob Bob, You should be getting zero when I6 and I7 are empty. It sounds as if there's a space or something like that in the cell. Some folks do that to clear a cell. -- Regards from Virginia Beach, Earl Kiosterud www.smokeyl.com ----------------------------------------------------------------------- "Bob Bob" <NOeMAILl@mail.com> wrote in me...

Recurring Booking Resource Problem
Hi there, When I book a meeting with a resource in recurring, I get an error: "Resource has declined your meeting because it is recurring" I thought it was a bug with my Exchange 2000 Server or my Office 2000. So, I just waited until... I upgraded my Exchange 2000 to Exchange 2003, and I upgrade my Office 2000 to Office 2003. But, the problem still exists. Any idea would appreciated. Ross YOu need to check the config file of the autoaccept agent. THere is an option to refuse recurring appointments "Ross" <nospam@ross.com> wrote in message news:7K2dnRGK8Yekd9...

Weird problem with Publisher's listing of available printers #2
None of the installed printers will show up in Publisher on this lady's computer. They are installed in Windows and show up fine in other Office Programs (Word, Excel, Powerpoint, etc.). However, when you go to print a document in Publisher, the only printers listed are the "Microsoft Office Document Image Writer" and the "Lanfax Printer". None of the physical printers are listed! I tried uninstalling and reinstalling all the printers. I also tried upgrading from Publisher XP to Publisher 2003, but the problem has persisted. I have googled this and searched Micr...

Problem wiih declared index in xml
In Access 2003, I exported a table in a file.XML, with shema (and with primary keys ans index informations) and data. One of the rules on indexes is that field 'Name' & 'First Name' must be unique. And I can see that is defined in the shema of the .xml file. Now, I read this file when opening a form in VS2005 with this code : ------------------------------- Dim dt As New DataSet dt.ReadXml("c:\temp\obj2.xml", XmlReadMode.ReadSchema) Me.DataGridView1.DataSource = dt.Tables("obj") ------------------------------- So, I see data in the DataGridView, and it ...