Help w/ SUMIF ; INDEX

[Robert]

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

 

[Jerry W. Lewis]

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]

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

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