# Sum up to certain value ???

#### Roelf

##### New Member

Hi there, it's been a while since had such a hard time figuring out what I need to do with certain formulas. My question is this. I have a sheet with days and the overtime that is worked on those days. The overtime is split up in 4 categories. OT, DT, PPH, PPH OT. Now the target for the overtime to be worked is 20, anything above that will be moved into "carry over" cells corresponding to the category that it was worked on. The problem I am having is, as the overtime gets worked it must be filled in dynamically into the for categories, but must not exceed 20 in total, anything above the 20, irrespective of the category, must be moved to carry over. I have link an example image of the sheet and what needs to happen. I have searched high and low and tried many solutions, but to no avail. I really hope someone can shed some light on what need to happen to get the formulas right.

I have asked on other forums also but no seems to understand my request. Direct link to image. https://imgur.com/5UEDTlB

First post BTW, hope the link and text is in order

Roelf

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### Eric W

##### MrExcel MVP
Welcome to the forum.

Try:

BCDEFGHIJKLMNOPQRSTUVWXY
3010203040506070809101112
4MonTueWedThuFriSatSunMonWedThuFriTarget20Carry Over
5O/T20.516O/TD/TPPHPPH O/TO/TD/TPPHPPH O/T
6D/T1118.51.50009.556
7PPH5
8PPH O/T6

<tbody>
</tbody>
Sheet5

Worksheet Formulas
CellFormula
P6=MIN(\$S\$4-SUM(\$O6:O6),SUM(INDEX(\$C5:\$N8,COLUMNS(\$P:P),0)))
U6=SUM(INDEX(\$C5:\$N8,COLUMNS(\$U:U),0))-P6

<tbody>
</tbody>

<tbody>
</tbody>

Put the P6 formula in and drag to S6, then put the U6 formula in and drag to X6.

Last edited:

#### Roelf

##### New Member
Wow this worked perfectly, amazing how simple it can be ... You helped me so much with this ! ! My GF asks if she can send cookies xd ...........

#### Eric W

##### MrExcel MVP
I've found that "simple" can be very hard to achieve, but sometimes another set of eyes really helps. Glad it works for you.

As far as the cookies, I wish there were a way to send them!
I'm a bit of a cookie-holic, I was actually eating one when I read your message! Maybe there's one left in the box . . .

#### Roelf

##### New Member
Hi there, sorry it's been a while before my response, I have done some testing and there seems to be a slight problem, at a glance it was allocating the hours correctly, but somehow it wants to make the 1.5 category full first, then move over to the other categories, I tried messing around with the formula but could not figure it out as to why it was happening, can we maybe have a look at it again ?

#### Eric W

##### MrExcel MVP
Can you give an example where it doesn't work as you expect?

#### Roelf

##### New Member
Can you give an example where it doesn't work as you expect?

I have uploaded a sample of how it should work with a brief explanation as to why, hope it makes sense. By the looks of what is happening, the target category first gets filled with the OT, then only DT or any other category, where as is should be, no matter where the data lies, it gets allocated as such.

https://imgur.com/a/NEvfIHv

#### KolGuyXcel

##### Board Regular
Just a quick question.

Among the 4 rows 5 through 8, will only 1 of them be filled in each column from C to N? Or could possibly be more than one rows?

Last edited:

#### Roelf

##### New Member
Just a quick question.

Among the 4 rows 5 through 8, will only 1 of them be filled in each column from C to N? Or could possibly be more than one rows?

Any one of the four categories per day. So if the guy works OT on one day, none of the other will be filled in on that day, this is applicable right across the board. So in short the answer is yes to your original question ... Does this help in any way ?

#### Eric W

##### MrExcel MVP
Yeah, that's a much tougher proposition. Try this:

BCDEFGHIJKLMNOPQRSTUVW
3123456789101112
4MonTueWedThuFriSatMonWedThuFriTarget20Carry Over
5O/T5555O/TD/TPPHPPH O/TO/TD/TPPHPPH O/T
6D/T5155005000
7PPH
8PPH O/T
9
106

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
P6=SUM(OFFSET(\$C\$5,MATCH(P\$5,\$B\$5:\$B\$8,0)-1,0,1,\$P10))-IF(INDEX(\$C\$5:\$O\$8,MATCH(P5,\$B\$5:\$B\$8,0),\$P10)=0,0,SUM(OFFSET(\$C\$5:\$C\$8,0,0,,\$P10))-\$R\$4)
T6=SUM(INDEX(\$C5:\$N8,COLUMNS(\$U:U),0))-P6

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
P10{=IFERROR(SMALL(IF(SUBTOTAL(9,OFFSET(C5:C8,0,0,,COLUMN(C4:N4)-COLUMN(C4)+1))>R4,COLUMN(C4:N4)-COLUMN(C4)+1),1),COLUMN(N4)-COLUMN(C4)+2)}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

I had to add a helper formula in P10 (which you can put anywhere, and hide if you want) which basically tells you which column is the one that first exceeds the target.
Then put the P6 formula in and drag to the right. Put the T6 formula in and drag to the right. The T6 formula is the same as before. Note that the P10 formula is an array formula.

This is fairly complicated, someone else could come up with something simpler. I'll look at it a bit more too.

Last edited:

Replies
1
Views
558
Replies
3
Views
2K
Replies
2
Views
335
Replies
3
Views
220
Replies
14
Views
360

1,186,112
Messages
5,955,905
Members
438,225
Latest member
rsur

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back