excel sum till a specific number

debugcn Published at Dev

Tomaž Podgornik

I am a total noob on excel so here i am looking for answers.

Best to explain this on a example.

I have in a row numbers from 1-1000000. I would like to sum them till 1000.

How do you do that?

Gary's Student

With values in row #1, in A2 enter:

=A1

In B2 enter:

=IF(A2="","",IF((A2+B1>1000),"",A2+B1))

and copy across. Finally in A3 enter:

=LOOKUP(88^99,2:2)

For example:

enter image description here

EDIT#1:

To avoid the "Helper" column, I would use the following UDF:

Public Function SumUntil(rIn As Range, Limit As Double) As Double
    Dim r As Range, t As Double
    SumUntil = 0
    For Each r In rIn
        t = SumUntil
        SumUntil = SumUntil + r.Value
        If SumUntil > Limit Then
            SumUntil = t
            Exit Function
        End If
    Next r
End Function

User Defined Functions (UDFs) are very easy to install and use:

ALT-F11 brings up the VBE window
ALT-I ALT-M opens a fresh module
paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

bring up the VBE window as above
clear the code out
close the VBE window

To use the UDF from Excel:

=SumUntil(1:1,1000)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

EDIT#2:

To count the number of values that were summed, I would use a different UDF:

Public Function CountUntil(rIn As Range, Limit As Double) As Long
    Dim r As Range, t As Double, i As Long
    Dim SumUntil As Double
    SumUntil = 0
    i = 0
    For Each r In rIn
        i = i + 1
        t = SumUntil
        SumUntil = SumUntil + r.Value
        If SumUntil > Limit Then
            CountUntil = i - 1
            Exit Function
        End If
    Next r
End Function

Collected from the Internet

Please contact [email protected] to delete if infringement.