Sum till????

[Bart Davis]

HELP? This is my first post so please let me know if I can clarify what I'm trying to learn to do.

I think in SQL they call this a SUM TILL. I think I figured out how to do it with nested IF statements but there has got to be a better way with INDEX, INDIRECT, MATCH, or OFFSET (maybe in an array?).

I think I want my formula to do the following:

=sum AU from this row up till sum of K from this row up is >= AR in this row, else "Out of Date Range"

Here's the (incomplete) way I figured out so far:

=IF(SUM(K314:K315)>=AR315,SUM(AU314:AU315),IF(SUM(K313:K315)>=AR315,AU313:AU315,IF(SUM(K312:K315)>=AR315,SUM(AU312:AU315),IF(SUM(K311:K315)>=AR315,SUM(AU311:AU315),IF and so on

I would like to be able to drag the formula down as new records are added to continue the calculation.

Thanks for any comments and help!

 

[Eric W]

Welcome to the forum.

What you are asking is probably just this side of impossible using native Excel formulas. I can vaguely picture one or two ways to do this, but it would either be enormously complicated, or require lots of helper columns. You could, however, create a UDF (user defined function) that does what you want much easier.

1) Open your workbook
2) Right click on the sheet tab on the bottom and select View Code
3) From the menu, click Insert > Module
4) Paste the following code into the window that opens

Public Function SumTill(val1 As Range, Col1 As Range, Col2 As Range)
Dim sum1 As Double, sum2 As Double, i As Long

    sum1 = 0
    sum2 = 0
    For i = val1.Row To 1 Step -1
        sum1 = sum1 + Col1.Range("A" & i)
        sum2 = sum2 + Col2.Range("A" & i)
        If sum1 >= val1.Value Then
            SumTill = sum2
            Exit Function
        End If
    Next i
    SumTill = "Out of Date Range"
    
End Function

5) Close the VBA editor by pressing Alt-Q.
6) Enter the formula in the cells you want. See the example below:

	K	AQ	AR	AS	AT	AU
1
2	1		10	Out of Date Range		1
3	2		10	Out of Date Range		2
4	3		10	Out of Date Range		4
5	4		10	15		8
6	5		10	28		16
7	6		10	48		32
8	7		10	96		64
9	8		16	224		128
10	9		17	384		256
11	10		35	992		512
12

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet19​

Worksheet Formulas Cell Formula AS2 =sumtill(AR2,K:K,AU:AU) AS3 =sumtill(AR3,K:K,AU:AU) AS4 =sumtill(AR4,K:K,AU:AU) AS5 =sumtill(AR5,K:K,AU:AU) AS6 =sumtill(AR6,K:K,AU:AU) AS7 =sumtill(AR7,K:K,AU:AU) AS8 =sumtill(AR8,K:K,AU:AU) AS9 =sumtill(AR9,K:K,AU:AU) AS10 =sumtill(AR10,K:K,AU:AU) AS11 =sumtill(AR11,K:K,AU:AU) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

You can copy/drag the formula as needed.

Hope this helps.

 

[Bart Davis]

THANKS!!!!
I've been able to get it to work on a sample of my data but haven't quiet figured out why it's throwing errors in the sheet with all my data. I'm sure I just need to check my referenced cells but haven had the time to do it yet. That's a very handy UDF for me so again, Thanks!

 

[Eric W]

Glad it helped. Let me know if you continue to have issues with it and maybe we can figure it out.

 

[Bart Davis]

I got it to work! I have a book that im going to try and use to play with the code more. I also discovered sumproduct(--(TODAY()........... from one of your other posts! that is awesome for me too!

Thanks Again!