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) and 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, LOOKUP, and most likely something else 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

Hi!

It's not very clear what it is you want to do.

Can you be more specific and tell us EXACTLY what you want
and what result you expect?

Biff

-----Original Message-----
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) and 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, LOOKUP, and most likely
something else 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
.

... the thing is, IF anywhere on Column A, there is a number
larger than 33, column C and E restart at Zero
plus whatever is in column B for that day.
....
Column D: Total from column C: minus 70

FWIW, going by the above lines and from studying the sample table given in
the original post for cols A to E, what's described below seems to return
the desired results in the computed cols C to E for the data input in cols A
and B ?? (But admit <g I couldn't correlate the part on the SUMIF example
mentioned for C7, E8, and the part about cols C/E meant to total the hours
on duty for the past 7/8 days. These parts were placed aside in coming up
with the set-up below .. )

Assume the data below is in A1:B11
(taken from the original post)

24 10
10 10
11 9
10 8
10 9
11 8
12 8
10 8
34 10
15 11
12 13

If you put

In C1: =B1
In C2: =IF(A2<=33,B2+C1,B2)
and copy C2 down to C11

In D1: =70-C1
and copy D1 down to D11

In E1: =B1
In E2: =IF(A2<=33,B2+E1,B2)
and copy E2 down to E11

(Col E is basically identical to col C)

The above will return in cols A to E,
the full pictu

24 10 10 60 10
10 10 20 50 20
11 9 29 41 29
10 8 37 33 37
10 9 46 24 46
11 8 54 16 54
12 8 62 8 62
10 8 70 0 70
34 10 10 60 10
15 11 21 49 21
12 13 34 36 34

which seems to correspond quite well with the results indicated in the
original post (Believe there's a typo in the value for C8 in the original
post: C8's computed value should be 70, not 60)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

On Mon, 7 Mar 2005 11:36:49 +0800, "Max" wrote:

... the thing is, IF anywhere on Column A, there is a number
larger than 33, column C and E restart at Zero
plus whatever is in column B for that day.
...
Column D: Total from column C: minus 70

FWIW, going by the above lines and from studying the sample table given in
the original post for cols A to E, what's described below seems to return
the desired results in the computed cols C to E for the data input in cols A
and B ?? (But admit <g I couldn't correlate the part on the SUMIF example
mentioned for C7, E8, and the part about cols C/E meant to total the hours
on duty for the past 7/8 days. These parts were placed aside in coming up
with the set-up below .. )

Assume the data below is in A1:B11
(taken from the original post)

24 10
10 10
11 9
10 8
10 9
11 8
12 8
10 8
34 10
15 11
12 13

If you put

In C1: =B1
In C2: =IF(A2<=33,B2+C1,B2)
and copy C2 down to C11

In D1: =70-C1
and copy D1 down to D11

In E1: =B1
In E2: =IF(A2<=33,B2+E1,B2)
and copy E2 down to E11

(Col E is basically identical to col C)

The above will return in cols A to E,
the full pictu

24 10 10 60 10
10 10 20 50 20
11 9 29 41 29
10 8 37 33 37
10 9 46 24 46
11 8 54 16 54
12 8 62 8 62
10 8 70 0 70
34 10 10 60 10
15 11 21 49 21
12 13 34 36 34

which seems to correspond quite well with the results indicated in the
original post (Believe there's a typo in the value for C8 in the original
post: C8's computed value should be 70, not 60)

Hi Max,

Actually, No, it is supposed to be 70, If at any time I take over 33
hours off duty, My time automatically starts back at 70, minus of
course any hours that I work that day.


If I have not exceeded the 70-hour limit and take 34 consecutive hours
off duty, I have 70 hours available again. I would then begin my
totaling on the day of the restart and not go back the full 7 or 8
days.

Column C is keeping a running total for only 7 days
Column E is doing the same but for 8 days
Unless of course I restart my 70 by taking 34 consecutive hours off
duty.

I would like to elaborate more about what I am trying to accomplish
with these formulas..

Thanks .. Robert
pacrat2001 <at msn <dot com

"Robert" wrote
.....
... (Believe there's a typo in the value for C8 in the original
post: C8's computed value should be 70, not 60)
....
Actually, No, it is supposed to be 70 ...

That means the suggested formulas in cols C to E returned correctly, yes?
....
I would like to elaborate more about what I am trying to accomplish
with these formulas..

Were the suggested formulas for cols C to E what you were after,
or is it something else ?

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

On Fri, 11 Mar 2005 17:09:44 +0800, "Max"
wrote:

"Robert" wrote
....
... (Believe there's a typo in the value for C8 in the original
post: C8's computed value should be 70, not 60)
...
Actually, No, it is supposed to be 70 ...

That means the suggested formulas in cols C to E returned correctly, yes?
...
I would like to elaborate more about what I am trying to accomplish
with these formulas..

Were the suggested formulas for cols C to E what you were after,
or is it something else ?


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