Keep Sum Positive Value until Negative Value appear

[ibmy]

Hi,

Does it possible to generate result in Column H?
From the past data, the max positive value appear (>=0) is below 100 row.
My data range between 250k-750k row.

6.8 - 12.xlsb
	G	H
1	Data	Sum+
2	-0.1
3	0
4	-0.1
5	0.1
6	0.1
7	0.1
8	0
9	0	0.4
10	-0.4
11	-0.2
12	0
13	0
14	0.2
15	0.1
16	0.1
17	0	0.4
18	-0.1
19	0.1
20	0.1
21	0.1
22	0.2
23	0.1
24	0.1
25	0
26	0
27	0
28	0
29	0
30	0.1
31	0.1
32	0.2
33	0
34	0.1
35	0.2
36	0.2
37	0.1
38	0.1
39	0.1
40	0
41	0
42	0
43	0.1
44	0.2
45	0.1
46	0.2
47	0.1
48	0
49	0.1
50	0
51	0.1
52	0.1
53	0.3	3.2
54	-0.1
55	0
56	0.2
57	0	0.2
58	-0.1
59	0.1
60	0.2
61	0.1
62	0.3
63	0	0.7
64	-0.2
Sheet1

 

[Sulprobil]

MrExcel_Keep Sum Positive Value until Negative Value appear.xlsx
	A	B
1	Data	Sum+
2	-0,1
3	0	0
4	-0,1
5	0,1
6	0,1
7	0,1
8	0
9	0	0,3
10	-0,4
11	-0,2
12	0
13	0
14	0,2
15	0,1
16	0,1
17	0	0,4
18	-0,1
19	0,1
20	0,1
21	0,1
22	0,2
23	0,1
24	0,1
25	0
26	0
27	0
28	0
29	0
30	0,1
31	0,1
32	0,2
33	0
34	0,1
35	0,2
36	0,2
37	0,1
38	0,1
39	0,1
40	0
41	0
42	0
43	0,1
44	0,2
45	0,1
46	0,2
47	0,1
48	0
49	0,1
50	0
51	0,1
52	0,1
53	0,3	3,2
54	-0,1
55	0
56	0,2
57	0	0,2
58	-0,1
59	0,1
60	0,2
61	0,1
62	0,3
63	0	0,7
64	-0,2
Sheet1

Cell Formulas
Range	Formula
B2:B64	B2	=IF(AND(NOT(ISBLANK(A2)),A2>=0,OR(A3<0,ISBLANK(A3))),SUM(INDEX(A$1:A2,1+MATCH(2,1/(A$1:A2<0),1)):A2),"")

For more than 10,000 rows you might prefer a VBA or a Power Query solution.

 

[Phuoc]

Or try

Book1
	G	H
1	Data	Sum+
2	-0.1
3	0	0
4	-0.1
5	0.1
6	0.1
7	0.1
8	0
9	0	0.3
10	-0.4
11	-0.2
12	0
13	0
14	0.2
15	0.1
16	0.1
17	0	0.4
18	-0.1
19	0.1
20	0.1
21	0.1
22	0.2
23	0.1
24	0.1
25	0
26	0
27	0
28	0
29	0
30	0.1
31	0.1
32	0.2
33	0
34	0.1
35	0.2
36	0.2
37	0.1
38	0.1
39	0.1
40	0
41	0
42	0
43	0.1
44	0.2
45	0.1
46	0.2
47	0.1
48	0
49	0.1
50	0
51	0.1
52	0.1
53	0.3	3.2
54	-0.1
55	0
56	0.2
57	0	0.2
58	-0.1
59	0.1
60	0.2
61	0.1
62	0.3
63	0	0.7
64	-0.2
Sheet1

Cell Formulas
Range	Formula
H2:H64	H2	=IF((G2>=0)*(G3<0),SUMIF($G$1:G2,">0")-SUM($H$1:H1),"")

 

[ibmy]

Thanks @Sulprobil @Phuoc
The formula works on small data.
For data range 250-750k, Excel is hang/stuck.

 

[Peter_SSs]

Give this a try

Sub Pos_Sums()
  Dim a As Variant, b As Variant
  Dim i As Long
  Dim dSum As Double
  
  a = Range("G2", Range("G" & Rows.Count).End(xlUp).Offset(1)).Value
  a(UBound(a), 1) = -1
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a) - 1
    If a(i, 1) >= 0 Then
      If a(i + 1, 1) < 0 Then
        b(i, 1) = dSum
        dSum = 0
      Else
        dSum = dSum + a(i, 1)
      End If
    End If
  Next i
  Range("H2").Resize(UBound(b)).Value = b
End Sub

 

[ibmy]

Thanks @Peter_SSs
The code works fast on 250-750k row data
Some result correct, some dont
Results which do not correct, just missing about 0.1-0.3 point
Mostly the result are work

Example of result that do work is on Row 53

 

[Phuoc]

....

 

[Phuoc]

A little edit:

Sub Pos_Sums()
  Dim a As Variant, b As Variant
  Dim i As Long
  Dim dSum As Double
 
  a = Range("G2", Range("G" & Rows.Count).End(xlUp).Offset(1)).Value
  a(UBound(a), 1) = -1
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a) - 1
    If a(i, 1) >= 0 Then
    dSum = dSum + a(i, 1)
      If a(i + 1, 1) < 0 Then
        b(i, 1) = dSum
        dSum = 0
      End If
    End If
  Next i
  Range("H2").Resize(UBound(b)).Value = b
End Sub

 

[ibmy]

Thanks @Phuoc for modify the code, the code working great
Also thanks to @Peter_SSs , original coder

May God bless all of you

 

[Peter_SSs]

Oops, Yes I did have my sum in the wrong place.
Thanks to @Phuoc for tidying that up.