MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Summing adjacent cells and starting the sum over at zeros


Posted by Eric Nelson on July 14, 2000 11:39 AM

I want to find the sum of a subset of adjacent cells that are set off by zeros.
For example data:

A1=2
A2=0
A3=1
A4=2
A5=4
A6=0
A7=1
A8=0
A9=0
A10=0
.
.
.

I need a sum result of 7 (1+2+4). I can not predict where the zeros will be in the column of data.


Posted by Eric Nelson on July 17, 0100 5:26 AM

Thanks, that is what I was lookling for.

Posted by Ryan on July 14, 0100 12:51 PM

Eric,

Try this!, it was working very well for me. I hope it does for you too. Let me know. It assumes the information is in column A, and that the number are continuous. YOu can change the columns if you need to. When you run this macro it will put the total below the last line in the column. Good luck

Ryan

Sub SumBetweenZeros()
Dim LastRow As Long
Dim x As Integer
Dim SumTotal As Long
Dim Sequence As Boolean

Application.ScreenUpdating = False
Sequence = False

LastRow = Range("A65000").End(xlUp).Row

SumTotal = 0
For x = 1 To LastRow
If Cells(x, 1).Value <> "0" Then
If Cells(x + 1, 1).Value <> "0" And Cells(x + 1, 1).Value <> "" Then
Sequence = True
End If
If Sequence = True And Cells(x + 1).Value = "0" Then _
SumTotal = SumTotal + Cells(x, 1).Value
If Sequence = True Then SumTotal = SumTotal + Cells(x, 1).Value
Else
Sequence = False
End If

Next x
Cells(LastRow + 2, 1).Value = SumTotal
Application.ScreenUpdating = True
End Sub

Posted by jrbee on July 14, 0100 2:04 PM

You could add an additional column that keeps a running total of the values in column a and resets when there is a zero. Example, in cell B1 put "=A1", then in cell B2 put "=IF(A1,B1+A2,A2)". Copy cell B2 down through cell B10. Now the contents of column B is a cummulative total.

Now in cell C1 put "=IF(AND(NOT(A1),B1),B1,"")". Copy this down through cell C10. Now column C contains only the subtotals you are looking for.