Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help w/ SUMIF ; INDEX
Hi, I was wondering if someone could help me out here. I am working
on a template and got part of the function working. Ex. C7 =SUMIF(A7:A1,"33",B7:B1) & E8 =SUMIF(A8:A1,"33",B8:B1). I have this part working, the thing is, IF anywhere on Column A, there is a number larger than 33, column C and E restart at Zero plus what ever is in column B for that day. I think I need to INDEX, and probably do more things too. Please, Any help would be greatly appreciated ! Thanks .. Column A: Off Duty Hours Column B: On Duty Hours Column C: Total hours on duty for past 7 days Column D: Total from column C: minus 70 Column E: Total hours on duty for past 8 days Numbers on the left are the days of the month A B C D E 1 24 10 10 60 10 2 10 10 20 50 20 3 11 9 29 41 29 4 10 8 37 33 37 5 10 9 46 24 46 6 11 8 54 16 54 7 12 8 62 8 62 8 10 8 60 0 70 9 34 10 10 60 10 10 15 11 21 49 21 11 12 13 34 36 34 |
#2
|
|||
|
|||
When you have intermediate results available, it is often eaiser to use
them than to try to do everything in a self-contained formula. Consider =B8+IF(A8<=33,C7-IF(COUNTIF(A3:A7,"33")=0,B1)) in C8 (and copied down) which simplifies to =B7+IF(A7<=33,C6) in C7 (and copied up) Jerry Robert wrote: Hi, I was wondering if someone could help me out here. I am working on a template and got part of the function working. Ex. C7 =SUMIF(A7:A1,"33",B7:B1) & E8 =SUMIF(A8:A1,"33",B8:B1). I have this part working, the thing is, IF anywhere on Column A, there is a number larger than 33, column C and E restart at Zero plus what ever is in column B for that day. I think I need to INDEX, and probably do more things too. Please, Any help would be greatly appreciated ! Thanks .. Column A: Off Duty Hours Column B: On Duty Hours Column C: Total hours on duty for past 7 days Column D: Total from column C: minus 70 Column E: Total hours on duty for past 8 days Numbers on the left are the days of the month A B C D E 1 24 10 10 60 10 2 10 10 20 50 20 3 11 9 29 41 29 4 10 8 37 33 37 5 10 9 46 24 46 6 11 8 54 16 54 7 12 8 62 8 62 8 10 8 60 0 70 9 34 10 10 60 10 10 15 11 21 49 21 11 12 13 34 36 34 |
#3
|
|||
|
|||
On Mon, 07 Mar 2005 08:59:53 -0500, "Jerry W. Lewis"
wrote: When you have intermediate results available, it is often eaiser to use them than to try to do everything in a self-contained formula. Consider =B8+IF(A8<=33,C7-IF(COUNTIF(A3:A7,"33")=0,B1)) in C8 (and copied down) which simplifies to =B7+IF(A7<=33,C6) in C7 (and copied up) Jerry Robert wrote: Hi, I was wondering if someone could help me out here. I am working on a template and got part of the function working. Ex. C7 =SUMIF(A7:A1,"33",B7:B1) & E8 =SUMIF(A8:A1,"33",B8:B1). I have this part working, the thing is, IF anywhere on Column A, there is a number larger than 33, column C and E restart at Zero plus what ever is in column B for that day. I think I need to INDEX, and probably do more things too. Please, Any help would be greatly appreciated ! Thanks .. Column A: Off Duty Hours Column B: On Duty Hours Column C: Total hours on duty for past 7 days Column D: Total from column C: minus 70 Column E: Total hours on duty for past 8 days Numbers on the left are the days of the month A B C D E 1 24 10 10 60 10 2 10 10 20 50 20 3 11 9 29 41 29 4 10 8 37 33 37 5 10 9 46 24 46 6 11 8 54 16 54 7 12 8 62 8 62 8 10 8 60 0 70 9 34 10 10 60 10 10 15 11 21 49 21 11 12 13 34 36 34 OKAY ! Im gonna try to explain in more detail! Column C only goes back 7 rows. Lets say, for C8 I need for it to look from A8:A2 for any number above 33. (For Cell C8) Example "If A2 is Greater than 33, I need for it to sum B2:B8." Example "If A3 is Greater than 33, I need for it to sum B3:B8" Example "If A4 is Greater than 33, I need for it to sum B4:B8" Example "If A5 is Greater than 33, I need for it to sum B5:B8" Example "If A6 is Greater than 33, I need for it to sum B6:B8" Example "If A7 is Greater than 33, I need for it to sum B7:B8" Example "If A8 is Greater than 33, I need for it to sum only B8" Column E goes back 8 rows. Lets say, for E8, I need for it to look at A8:A1 for any number above 33. (For Cell E8) Example " If A1 I need for it to sum B1:B8." Example " If A2 is Greater than 33, , I need for it to sum B2:B8" Example " If A3 is Greater than 33, , I need for it to sum B3:B8" Example " If A4 is Greater than 33, , I need for it to sum B4:B8" Example " If A5 is Greater than 33, , I need for it to sum B5:B8" Example " If A6 is Greater than 33, , I need for it to sum B6:B8" Example " If A7 is Greater than 33, , I need for it to sum B7:B8" Example " If A8 is Greater than 33, , I need for it to sum B8" Thanks .. Robert pacrat2001 <at msn <dot com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Min formula not returning value from Index | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions | |||
index to a range of cells | Excel Worksheet Functions |