# Suming numbers until a positive number is reached

#### cjw6886

##### New Member
Hello,

I need a formula to reach the output below. I need to have the cells sum the numbers before it that are negative and have it stop once it reaches a positive number. Also, if the number is negative I need it to end up as 0. Thanks in advance for any contribution you make to helping me get this figured out!

Data
200 400 -100 -300 500 600 -200 700

Output
200 400 0 0 100 600 0 500

#### shreif

##### New Member

<colgroup><col style="width:48pt" width="64" span="8"> </colgroup><tbody>

</tbody>
 200 400 -100 -300 500 600 -200 700 200 400 0 0 200 600 0 500

<tbody>
</tbody>

highlight select your target range manually , then run the below code,

Code:
``````Sub Shreif11Nov2016()
Dim rng, rng1 As Range
Dim i, c As Long
For Each rng In Selection
If rng > 0 Then
rng.Offset(1) = rng
Else
rng.Offset(1) = 0
End If
Next
Selection.Offset(1).Select
For Each rng1 In Selection
If i < 0 And rng1 <> 0 Then
rng1 = rng1 + c
i = 0
c = 0
End If
If rng1 = 0 Then
i = rng1.Offset(-1)
c = c + i
End If
Next
End Sub``````

#### bosco_yip

##### Well-known Member
Try,

1] Left A1 blank, your data housed in B1:I1

2] In B2, formula copy across :

=IF(B1>0,IF(A1<0,MIN(A1+B1,B1),B1),0)

Regards

• deciog

##### Well-known Member
Hi,

1] Left A1 blank, your data housed in B1:I1

2] In B2, formula copy across :

=IF(B1<0,0,IF(A1<0,SUM(\$B\$1:B1)-SUM(A\$2:\$B2),B1))

#### cjw6886

##### New Member
Thanks Admiral! That is exactly what i needed

you're welcome