Sum Amount till max value greater than 1

[motilulla]

Hello,

I have an amount in columns "A2" to below down side

I want a formula in cells “B2” Below down which can sum amount till max value greater than 1

For example sun first amount staring from A2:A12 which is 1 to max 512 = 1023 result sum in column B, sum second amount staring from A13:B18 which is 1 to max 32 = 63 result sum in column B….and so on

For a more detail the image is attached. Pease suggest formula

*	A	B
1	Amount	Sum
2	1	1
3	2	3
4	4	7
5	8	15
6	16	31
7	32	63
8	64	127
9	128	255
10	256	511
11	512	1.023
12	1	1
13	2	3
14	4	7
15	8	15
16	16	31
17	32	63
18	1	1
19	1	2
20	1	3
21	1	4
22	2	6
23	4	10
24	8	18
25	16	34
26	32	66
27	1	1
28	1	2
29	1	3
30	1	4
31	1	5
32	2	7
33	4	11
34	8	19
35	16	35
36	32	67
37	64	131
38	1	1
39	1	2
40	1	3
41	1	4

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B
1	Amount	Sum
2	1	1
3	2	3
4	4	7
5	8	15
6	16	31
7	32	63
8	64	127
9	128	255
10	256	511
11	512	1023
12	1	1
13	2	3
14	4	7
15	8	15
16	16	31
17	32	63
18	1	1
19	1	2
20	1	3
21	1	4
22	2	6
23	4	10
24	8	18
25	16	34
26	32	66
27	1	1
28	1	2
29	1	3
30	1	4
31	1	5
32	2	7
33	4	11
34	8	19
35	16	35
36	32	67
37	64	131
38	1	1
39	1	2
40	1	3
41	1	4
Current

Cell Formulas
Range	Formula
B2:B41	B2	=IF(A2<A1,A2,A2+B1)

 

[motilulla]

How about

+Fluff 1.xlsm
	A	B
1	Amount	Sum
2	1	1
3	2	3
4	4	7
5	8	15
6	16	31
7	32	63
8	64	127
9	128	255
10	256	511
11	512	1023
12	1	1
13	2	3
14	4	7
15	8	15
16	16	31
17	32	63
18	1	1
19	1	2
20	1	3
21	1	4
22	2	6
23	4	10
24	8	18
25	16	34
26	32	66
27	1	1
28	1	2
29	1	3
30	1	4
31	1	5
32	2	7
33	4	11
34	8	19
35	16	35
36	32	67
37	64	131
38	1	1
39	1	2
40	1	3
41	1	4
Current

Cell Formulas
Range	Formula
B2:B41	B2	=IF(A2<A1,A2,A2+B1)

Fluff, Formula worked as treat! Thank you for your help

Good Luck and have a good day!

Kind Regards,
Moti

 

[Georgiboy]

Not sure why but I always go in with code, seems to be the only tool in my toolbox, solved now but code below anyway:

Sub test()
    Dim rng As Range, var As Variant, x As Long, s As Double, tmp As Double
    
    Set rng = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    var = rng.Value
    
    For x = 1 To UBound(var)
        If var(x, 1) < tmp Then
            s = 0
        End If
        s = s + var(x, 1)
        var(x, 2) = s
        tmp = var(x, 1)
    Next x
    rng = var
End Sub

 

[motilulla]

Not sure why but I always go in with code, seems to be the only tool in my toolbox, solved now but code below anyway:

Sub test()
    Dim rng As Range, var As Variant, x As Long, s As Double, tmp As Double
  
    Set rng = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    var = rng.Value
  
    For x = 1 To UBound(var)
        If var(x, 1) < tmp Then
            s = 0
        End If
        s = s + var(x, 1)
        var(x, 2) = s
        tmp = var(x, 1)
    Next x
    rng = var
End Sub

Georgiboy, macro worked fine! but I will go with formula because it is practical for small job for which I need at the movement.

Any ways thank you for your help some day may I need for Large file to work with it.

Good Luck and have a good day!

Kind Regards,
Moti

 

[Fluff]

Glad we could help & thanks for the feedback.