Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula problem | Excel Worksheet Functions | |||
Formula problem | Excel Discussion (Misc queries) | |||
Formula problem | Excel Discussion (Misc queries) | |||
Formula Problem | Excel Worksheet Functions | |||
formula Problem | Excel Discussion (Misc queries) |