Sum up to certain value ???

[Roelf]

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

 

[KolGuyXcel]

@Eric W

Check the formula to be used in T6! It has a range "$U:U". Perhaps that needs correction. It's not working for the carry over cells.

 

[KolGuyXcel]

I have a solution with codes! Check if it suits! It assumes that the data would be in the grid C5:N8. And the outputs would be in the cells P6:W6.

Sub target_meet()
Dim cum_sum As Double, cum_sumprev As Double
Dim counterr As Integer, counterc As Integer, tgt_blk As Integer, crov_blk As Integer
Dim loop_brk As Boolean
loop_brk = False
cum_sum = 0
For counterc = 3 To 14
For counterr = 5 To 8
cum_sum = cum_sum + Cells(counterr, counterc).Value2
If cum_sum <= 20 Then
cum_sumprev = cum_sum
Else
loop_brk = True
Exit For
End If
Next
If loop_brk Then
Exit For
End If
Next
For tgt_blk = 1 To 4
If tgt_blk < counterr - 4 Then
Cells(6, 15 + tgt_blk) = WorksheetFunction.Sum(Range(Cells(4 + tgt_blk, 3), Cells(4 + tgt_blk, counterc)))
Else
Cells(6, 15 + tgt_blk) = 20 - WorksheetFunction.Sum(Range(Cells(6, 16), Cells(6, 15 + tgt_blk - 1)))
Exit For
End If
Next
Debug.Print tgt_blk
For tgt_blk = (tgt_blk + 1) To 4
Cells(6, 15 + tgt_blk) = 0
Next
For crov_blk = 1 To 4
Cells(6, 19 + crov_blk) = WorksheetFunction.Sum(Range(Cells(4 + crov_blk, 3), Cells(4 + crov_blk, 14))) - Cells(6, 15 + crov_blk).Value2
Next
End Sub


HTH!

 

[Eric W]

Good eye. For better optics, I should have put $T:T instead of $U:U in the T formula. However, since that's just used to create a counter, $U:U should work just fine. Are you not getting expected results in T:W?

 

[KolGuyXcel]

@Eric W

Sorry for the bother. My bad. Your formula solution is working fine. I can't think of why I was getting incorrect results when I commented.

Perhaps, it's because when I started working on this problem, I kept creating tables of dummy data myself assuming that there could be multiple values in each of the columns C to N. And then I modified the tables after getting the response #9 from Roelf where he mentioned that there could be only 1 value in each of those columns. But I think I didn't modify them properly while checking your formulae! Sorry Again!

By the way, the code seems to work if even if there are multiple values in each of the columns C to N.

 

[KolGuyXcel]

A slight modification is needed in my earlier code!

Sub target_meet()
Dim cum_sum As Double, cum_sumprev As Double, sum_lf As Double
Dim counterr As Integer, counterc As Integer, tgt_blk As Integer, crov_blk As Integer, lf_c As Integer
Dim loop_brk As Boolean
loop_brk = False
cum_sum = 0
sum_lf = 0
For counterc = 3 To 14
For counterr = 5 To 8
cum_sum = cum_sum + Cells(counterr, counterc).Value2
If cum_sum < 20 Then
cum_sumprev = cum_sum
Else
loop_brk = True
Exit For
End If
Next
If loop_brk Then
Exit For
End If
Next
For tgt_blk = 1 To 4
If tgt_blk < counterr - 4 Then
Cells(6, 15 + tgt_blk) = WorksheetFunction.Sum(Range(Cells(4 + tgt_blk, 3), Cells(4 + tgt_blk, counterc)))
ElseIf tgt_blk = counterr - 4 Then
For lf_c = 1 To counterr - 5
sum_lf = sum_lf + Cells(4 + lf_c, counterc).Value2
Next
Cells(6, 15 + tgt_blk) = 20 - WorksheetFunction.Sum(Range(Cells(5, 3), Cells(8, counterc - 1))) - sum_lf + WorksheetFunction.Sum(Range(Cells(4 + tgt_blk, 3), Cells(4 + tgt_blk, counterc - 1)))
Else
Cells(6, 15 + tgt_blk) = WorksheetFunction.Sum(Range(Cells(4 + tgt_blk, 3), Cells(4 + tgt_blk, counterc - 1)))
End If
Next
For crov_blk = 1 To 4
Cells(6, 19 + crov_blk) = WorksheetFunction.Sum(Range(Cells(4 + crov_blk, 3), Cells(4 + crov_blk, 14))) - Cells(6, 15 + crov_blk).Value2
Next
End Sub

 

[Roelf]

A slight modification is needed in my earlier code!

Sub target_meet()
Dim cum_sum As Double, cum_sumprev As Double, sum_lf As Double
Dim counterr As Integer, counterc As Integer, tgt_blk As Integer, crov_blk As Integer, lf_c As Integer
Dim loop_brk As Boolean
loop_brk = False
cum_sum = 0
sum_lf = 0
For counterc = 3 To 14
For counterr = 5 To 8
cum_sum = cum_sum + Cells(counterr, counterc).Value2
If cum_sum < 20 Then
cum_sumprev = cum_sum
Else
loop_brk = True
Exit For
End If
Next
If loop_brk Then
Exit For
End If
Next
For tgt_blk = 1 To 4
If tgt_blk < counterr - 4 Then
Cells(6, 15 + tgt_blk) = WorksheetFunction.Sum(Range(Cells(4 + tgt_blk, 3), Cells(4 + tgt_blk, counterc)))
ElseIf tgt_blk = counterr - 4 Then
For lf_c = 1 To counterr - 5
sum_lf = sum_lf + Cells(4 + lf_c, counterc).Value2
Next
Cells(6, 15 + tgt_blk) = 20 - WorksheetFunction.Sum(Range(Cells(5, 3), Cells(8, counterc - 1))) - sum_lf + WorksheetFunction.Sum(Range(Cells(4 + tgt_blk, 3), Cells(4 + tgt_blk, counterc - 1)))
Else
Cells(6, 15 + tgt_blk) = WorksheetFunction.Sum(Range(Cells(4 + tgt_blk, 3), Cells(4 + tgt_blk, counterc - 1)))
End If
Next
For crov_blk = 1 To 4
Cells(6, 19 + crov_blk) = WorksheetFunction.Sum(Range(Cells(4 + crov_blk, 3), Cells(4 + crov_blk, 14))) - Cells(6, 15 + crov_blk).Value2
Next
End Sub

Hi guys, so I have tried the new solution from Eric and it works as I expected, I had to modify the ranges of the formulas a bit to accommodate 31 columns. I have not yet tried the VBA solution, because my company has some strict rules on VBA workbooks. I have uploaded the file we will be using at work with the adjusted formulas, you can have a look at the file and if it still matches the original formula you provided Eric.

Link to file
https://drive.google.com/file/d/1F6Q7ehL2ZaII7nI_IOAyiPFXgTuSal4t/view?usp=sharing

Thanks a million for all the positive inputs, this is by far the best excel forum I have visited and interacted with. If you cant access the file for some reason just let me know, I can mail you straight up ! !

 

[KolGuyXcel]

Try these!

In Cell AK14 enter as array formula (Ctrl+Shift+Enter)
=IFERROR(SMALL(IF(SUBTOTAL(9,OFFSET(C9:C12,0,0,,COLUMN(C9:AG9)-COLUMN(C9)+1))>20,COLUMN(C9:AG9)-COLUMN(C9)+1),1),COLUMN(AG9)-COLUMN(C9)+2)

In Cell AK11
=SUM(OFFSET($C$9,MATCH(AK10,$B$9:$B$12,0)-1,0,1,$AK14))-IF(INDEX($C$9:$AH$12,MATCH(AK10,$B$9:$B$12,0),$AK14)=0,0,SUM(OFFSET($C$9:$C$12,0,0,,$AK14))-20)

In Cell AP11
=SUM(INDEX($C9:$AG12,COLUMNS($AQ:AQ),0))-AK11

 

[KolGuyXcel]

Okay! I see that you have to use these formulae across the entire sheet for multiple sets of data! I'll modify the absolute and relative references in the formulae accordingly!

In Cell AK14 (Array Formula)
=IFERROR(SMALL(IF(SUBTOTAL(9,OFFSET(C9:C12,0,0,,COLUMN(C9:AG9)-COLUMN(C9)+1))>20,COLUMN(C9:AG9)-COLUMN(C9)+1),1),COLUMN(AG9)-COLUMN(C9)+2)

In Cell AK11
=SUM(OFFSET($C9,MATCH(AK10,$B9:$B12,0)-1,0,1,$AK14))-IF(INDEX($C9:$AH12,MATCH(AK10,$B9:$B12,0),$AK14)=0,0,SUM(OFFSET($C9:$C12,0,0,,$AK14))-20)

In Cell AP11
=SUM(INDEX($C9:$AG12,COLUMNS($AQ:AQ),0))-AK11


The cells to be used per set are
AK14, AK11, AP11
AK33, AK30, AP30
AK52, AK49, AP49
AK71, AK68, AP68
and so on!