hi guys (thanks for loop help) part 2: cumulative summing/counting

[Sam]

Hey Guys, need some greater minds than mine to help me out....

so I need it to cumulatively sum the 1's in column a but the moment there
is a 0 it needs to stop summing.Then once there is a 1, start the process
from 1 again. This is going from the bottom to the top by the way.

I NEED THIS
COLUMN BELOW
(B) BUT CAN'T
WORK OUT THE
RIGHT FORMULA

THIS COLUMN (C)
IS MY FIRST TRY..
BUT CAN'T GET IT
TO RESTART THE
SUM PROPERLY

A B C

1 4 13
1 3 13
1 2 12
1 1 11
0 0 10
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
1 2 0
1 1 9
0 0 8
0 0 0
1 2 0
1 1 7
0 0 6
0 0 0
0 0 0
0 0 0
1 6 0
1 5 5
1 4 4
1 3 3
1 2 2
1 1 IF(B32>0,SUM(B32:B$32),0)


much appreciated anyone? This could also be in VBA if anyone prefers?

 

[Mike H]

Sam

I see you have this posted in several places. There's no need to do that,
you wiil get an answer and won't waste peoples time answering in other
threads when you already have an answer.

The test data you posted goes down as far as A26 so put this in B26 and drag
up

=IF(A26=1,A26+(B27),0)

Note B27 must be empty to start the formula off.

Mike

 

[ryguy7272]

This counts down:

Sub subtotaltest()

Dim lRow As Long
Dim cell As Range
Dim RowCount As Long

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

lRow = Range("A65536").End(xlUp).Row + 1
RowCount = 0
For Each cell In Range("A2:A" & lRow)
If cell.Value = 0 Then
cell.Offset(0, 1).FormulaR1C1 = "=SUM(R[" & -RowCount & "]C[-1]:R[-1]C[-1])"
RowCount = 0
Else
RowCount = RowCount + 1
End If
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

**NOTICE!! Make a backup of your data before running, in case the outcome
is unexpected...I wouldn't want you to lose any data!!

HTH,
Ryan---