# SUM dynamically till a condition is reached?

#### suresh.79

##### New Member
hi

i want to dynamically SUM cells till a certain condition is reached.

Cell A1 has a value y

Cell B1 has value x1, B2 has value x2, B3 has value x3, B4 has value x4 and so on

I want to write a formula in cell C1 which will keep on summing x1, x2, x3 etc till the sum value is just greater than or equal to y

eg A1 = 100, B1 = 50, B2 = 49, B3 = 5, B4 = 10, B5 = 41 ...

so Cell C1 should give me the sum as (50+49+5) = 104 (ie keep on summing until the sum just becomes greater than or equal to 100)

#### Clarity

##### New Member
Hi,

I have written some VBA that will do this for you. You can pick up an example file here:

http://cid-4702efbc51549018.skydrive.live.com/self.aspx/Chandoo%20Forum/VBA%20Dynamic%20range%20based%20on%20value.xls

Or here is the code:

You will need to define A1 as named range limit.

Sub NamedRange()

Dim Rcell, Ccell, rng As Range

Set Rcell = Cells(1, 2)

Set Ccell = Cells(1, 2)

Dim limit As Integer

Dim i As Integer

limit = Range("limit").Value

i = 1

Set rng = Range(Rcell, Ccell)

Do While Application.WorksheetFunction.Sum(Range("range")) &#60; limit

Set Rcell = Cells(1 + i, 2)

Set rng = Range(Rcell, Ccell)

i = i + 1

Loop

End Sub

#### Clarity

##### New Member
Hi,

I have also added a non VBA solution into the file.

The non VBA solution uses a "helper" column to identify the cells to include in the sum and then uses the SUMIF forumula to calculate the total.

Helper formula:

=IF(SUM(\$B\$1:B1)&#60;Limit,1,0)

SUMIF

=SUMIF(C1:C11,1,B1:B11)

Myles