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

"Robert" wrote in message
.....
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"

Here's my best shot at it ..

Put in C8:

=IF(A233,SUM(B2:B8),IF(A333,SUM(B3:B8),IF(A433, SUM(B4:B8),IF(A533,SUM(B5
:B8),IF(A633,SUM(B6:B8),IF(A733,SUM(B7:B8),IF(A8 33,B8,0)))))))

Copy C8 down

Put in E8:

=IF(A133,SUM(B1:B8),IF(A233,SUM(B2:B8),IF(A333, SUM(B3:B8),IF(A433,SUM(B4
:B8),IF(A533,SUM(B5:B8),IF(A633,SUM(B6:B8),IF(A7 33,SUM(B7:B8),IF(A833,B8
,0))))))))

Copy E8 down

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

How about something like this...

C8:

=SUM(INDEX(B2:B8,MATCH(TRUE,A2:A833,0)):B8)

E8:

=SUM(INDEX(B1:B8,MATCH(TRUE,A1:A833,0)):B8)

Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER, not
just ENTER.

Hope this helps!

In article ,
Robert wrote:

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

Okay, Nice work Domenic

The formula is almost doing what I need it to do, but, if there
is not a number larger than 33 in any of the A cells ex. A8:A2, Excel
is giving me an error #N/A. If there is not a number larger than 33, I
need all the corresponding cells calculated together. Also I changed
the formula to start looking from A8:A2 instead of A2:A8, I meant to
put them down the right way in my previous post.
No Biggie, I changed that.

(For Cell C8)
Example "If A8 is Greater than 33, I need for it to sum only B8"
Example "If A7 is Greater than 33, I need for it to sum B7:B8"
Example "If A6 is Greater than 33, I need for it to sum B6:B8"
Example "If A5 is Greater than 33, I need for it to sum B5:B8"
Example "If A4 is Greater than 33, I need for it to sum B4:B8"
Example "If A3 is Greater than 33, I need for it to sum B3:B8"
Example "If A2 is Greater than 33, I need for it to sum B2:B8."

=SUM(INDEX(B8:B2,MATCH(TRUE,A8:A233,0)):B8)

One more thing I did notice, when it finds the very first A cell with
a number larger than 33, I need for excel to go no further than that
line for the calculations, I noticed that if there are more than one
cell in A8:A1 with a 34, it calculates from the very last one. In
other words, If A5 and A3 both have a value greater than 33, I need
for the formula to stop at A5 and calculate only from C5:C8.

Thanks so much for your help so far, I would never have gotten this
far. Thankyou ! Robert


On Fri, 11 Mar 2005 23:44:10 -0500, Domenic
wrote:

How about something like this...

C8:

=SUM(INDEX(B2:B8,MATCH(TRUE,A2:A833,0)):B8)

E8:

=SUM(INDEX(B1:B8,MATCH(TRUE,A1:A833,0)):B8)

Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER, not
just ENTER.

Hope this helps!

In article ,
Robert wrote:

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

On Sat, 12 Mar 2005 11:58:14 +0800, "Max"
wrote:

"Robert" wrote in message
....
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"

Here's my best shot at it ..

Put in C8:

I have also done this myself, this one would work too, except I need
to add 1 more function to this, then it no longer works. Only 7
functions are allowed per cell.
=IF(A233,SUM(B2:B8),IF(A333,SUM(B3:B8),IF(A433 ,SUM(B4:B8),IF(A533,SUM(B5
:B8),IF(A633,SUM(B6:B8),IF(A733,SUM(B7:B8),IF(A 833,B8,0)))))))

Copy C8 down

Put in E8:

This one has 8 functions, as I said above, Only 7 functions allowed
per cell.
=IF(A133,SUM(B1:B8),IF(A233,SUM(B2:B8),IF(A333 ,SUM(B3:B8),IF(A433,SUM(B4
:B8),IF(A533,SUM(B5:B8),IF(A633,SUM(B6:B8),IF(A 733,SUM(B7:B8),IF(A833,B8
,0))))))))

Copy E8 down


Thanks for your input ... Robert

Try...

=IF(COUNTIF(A2:A8,"33"),SUM(INDEX(B2:B8,LARGE(IF( A2:A833,ROW(A2:A8)-ROW
(A2)+1),1)):B8),SUM(B2:B8))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
Robert wrote:

Okay, Nice work Domenic

The formula is almost doing what I need it to do, but, if there
is not a number larger than 33 in any of the A cells ex. A8:A2, Excel
is giving me an error #N/A. If there is not a number larger than 33, I
need all the corresponding cells calculated together. Also I changed
the formula to start looking from A8:A2 instead of A2:A8, I meant to
put them down the right way in my previous post.
No Biggie, I changed that.

(For Cell C8)
Example "If A8 is Greater than 33, I need for it to sum only B8"
Example "If A7 is Greater than 33, I need for it to sum B7:B8"
Example "If A6 is Greater than 33, I need for it to sum B6:B8"
Example "If A5 is Greater than 33, I need for it to sum B5:B8"
Example "If A4 is Greater than 33, I need for it to sum B4:B8"
Example "If A3 is Greater than 33, I need for it to sum B3:B8"
Example "If A2 is Greater than 33, I need for it to sum B2:B8."

=SUM(INDEX(B8:B2,MATCH(TRUE,A8:A233,0)):B8)

One more thing I did notice, when it finds the very first A cell with
a number larger than 33, I need for excel to go no further than that
line for the calculations, I noticed that if there are more than one
cell in A8:A1 with a 34, it calculates from the very last one. In
other words, If A5 and A3 both have a value greater than 33, I need
for the formula to stop at A5 and calculate only from C5:C8.

Thanks so much for your help so far, I would never have gotten this
far. Thankyou ! Robert

Dominic,

Excellent work, That really works great. Do I owe you anything for
your time and trouble?

BTW, what is it with using CONTROL+SHIFT+ENTER ?
What is the concept of this?

Can I ask you a favor? Could you explain to me the formula that you
came up with in more detail so that I may try to understand it better?
If you like you can email me at the email address I have provided in
these replies.

Thanks again so much Dominic, ..... Robert

On Sat, 12 Mar 2005 11:41:55 -0500, Domenic
wrote:

Try...

=IF(COUNTIF(A2:A8,"33"),SUM(INDEX(B2:B8,LARGE(IF (A2:A833,ROW(A2:A8)-ROW
(A2)+1),1)):B8),SUM(B2:B8))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
Robert wrote:

Okay, Nice work Domenic

The formula is almost doing what I need it to do, but, if there
is not a number larger than 33 in any of the A cells ex. A8:A2, Excel
is giving me an error #N/A. If there is not a number larger than 33, I
need all the corresponding cells calculated together. Also I changed
the formula to start looking from A8:A2 instead of A2:A8, I meant to
put them down the right way in my previous post.
No Biggie, I changed that.

(For Cell C8)
Example "If A8 is Greater than 33, I need for it to sum only B8"
Example "If A7 is Greater than 33, I need for it to sum B7:B8"
Example "If A6 is Greater than 33, I need for it to sum B6:B8"
Example "If A5 is Greater than 33, I need for it to sum B5:B8"
Example "If A4 is Greater than 33, I need for it to sum B4:B8"
Example "If A3 is Greater than 33, I need for it to sum B3:B8"
Example "If A2 is Greater than 33, I need for it to sum B2:B8."

=SUM(INDEX(B8:B2,MATCH(TRUE,A8:A233,0)):B8)

One more thing I did notice, when it finds the very first A cell with
a number larger than 33, I need for excel to go no further than that
line for the calculations, I noticed that if there are more than one
cell in A8:A1 with a 34, it calculates from the very last one. In
other words, If A5 and A3 both have a value greater than 33, I need
for the formula to stop at A5 and calculate only from C5:C8.

Thanks so much for your help so far, I would never have gotten this
far. Thankyou ! Robert

....
I have also done this myself, this one would work too, except I need
to add 1 more function to this, then it no longer works. Only 7
functions are allowed per cell.

You should have posted this formula in the first instance as a start point
....
Anyway, can see that you've got some nice help from Domenic
....

Thanks for your input ... Robert

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

In article ,
Robert wrote:

Do I owe you anything for
your time and trouble?

Thanks is more than enough. :)

BTW, what is it with using CONTROL+SHIFT+ENTER ?
What is the concept of this?

See Excel's help menu for a detailed explanation. Search for...

- Array formula
- About array formulas and how to enter them

Could you explain to me the formula that you
came up with in more detail so that I may try to understand it better?

Let's first take a look at the LARGE part of the formula. If A2:B8
contains the following values...

20 1
28 2
16 3
35 4
10 5
12 6
33 7

A2:A833 returns the following array...

FALSE
FALSE
FALSE
TRUE
FALSE
FALSE
FALSE

ROW(A2:A8)-ROW(A2)+1 returns...

1
2
3
4
5
6
7

IF(A2:A833,ROW(A2:A8)-ROW(A2)+1) returns...

FALSE
FALSE
FALSE
4
FALSE
FALSE
FALSE

Therefore, LARGE(IF(A2:A833,ROW(A2:A8)-ROW(A2)+1),1) returns 4, which
is used by INDEX to refer to the fourth cell in the range B2:B8. The
reference B5 is returned. Then we end up with the following...

SUM(B5:B8)

Hope this helps!