Help with SUMIF, INDEX, LOOKUP Please !!

[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) 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

 

[Biff]

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

 

[Max]

... 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 picture:

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)

 

[Robert]

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 picture:

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

 

[Max]

....
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 ?

 

[Robert]

That means the suggested formulas in cols C to E returned correctly, yes?
...

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

 

[Max]

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(A2>33,SUM(B2:B8),IF(A3>33,SUM(B3:B8),IF(A4>33,SUM(B4:B8),IF(A5>33,SUM(B5
:B8),IF(A6>33,SUM(B6:B8),IF(A7>33,SUM(B7:B8),IF(A8>33,B8,0)))))))

Copy C8 down

Put in E8:

=IF(A1>33,SUM(B1:B8),IF(A2>33,SUM(B2:B8),IF(A3>33,SUM(B3:B8),IF(A4>33,SUM(B4
:B8),IF(A5>33,SUM(B5:B8),IF(A6>33,SUM(B6:B8),IF(A7>33,SUM(B7:B8),IF(A8>33,B8
,0))))))))

Copy E8 down

 

[Domenic]

How about something like this...

C8:

=SUM(INDEX(B2:B8,MATCH(TRUE,A2:A8>33,0)):B8)

E8:

=SUM(INDEX(B1:B8,MATCH(TRUE,A1:A8>33,0)):B8)

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

Hope this helps!

 

[Robert]

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:A2>33,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

 

[Robert]

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(A2>33,SUM(B2:B8),IF(A3>33,SUM(B3:B8),IF(A4>33,SUM(B4:B8),IF(A5>33,SUM(B5
:B8),IF(A6>33,SUM(B6:B8),IF(A7>33,SUM(B7:B8),IF(A8>33,B8,0)))))))

Copy C8 down

Put in E8:

This one has 8 functions, as I said above, Only 7 functions allowed
per cell.

=IF(A1>33,SUM(B1:B8),IF(A2>33,SUM(B2:B8),IF(A3>33,SUM(B3:B8),IF(A4>33,SUM(B4
:B8),IF(A5>33,SUM(B5:B8),IF(A6>33,SUM(B6:B8),IF(A7>33,SUM(B7:B8),IF(A8>33,B8
,0))))))))

Copy E8 down

Thanks for your input ... Robert

 

[Domenic]

Try...

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

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

Hope this helps!

 

[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

 

[Max]

....

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 !

 

[Domenic]

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:A8>33 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:A8>33,ROW(A2:A8)-ROW(A2)+1) returns...

FALSE
FALSE
FALSE
4
FALSE
FALSE
FALSE

Therefore, LARGE(IF(A2:A8>33,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!