Summing Contiguous Values in a Column?

[Confused_in_Houston]

I have groupings of data in column A. Each group is obviously continguous.
The data grouping are separated by a single blank cell in the column. I
would like to sum the values in each group.

What I have:

A
1000
500
300
200

1000
400
100

1000
1000

What I'd like:

A B
1000
500
300
200
2000
1000
400
100
1500
1000
1000
2000

thanks!

 

[Dave Peterson]

I'd use a macro.

If that's ok:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myNumbers As Range
Dim myArea As Range
Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

With wks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

Set myNumbers = Nothing
On Error Resume Next
Set myNumbers = myRng.Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
On Error GoTo 0

If myNumbers Is Nothing Then
MsgBox "No number constants in column A!"
Exit Sub
End If

For Each myArea In myNumbers.Areas
With myArea
.Resize(1, 1).Offset(.Rows.Count, 1).Formula _
= "=sum(" & .Address(0, 0) & ")"
End With
Next myArea

End Sub



Select the range to fix first and run it.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Jarek Kujawa]

Dave's solution is surely more versatile
without going to VBA you might try the following formula (provided yr
data starts from A1 which is blank, then A2=1000, A3=500, etc.:

=SUM(IF(ISBLANK(A2),OFFSET(A2,-1,,-(MIN(IF(ISBLANK($A$1:A2),ROW()-ROW
($A$1:A2)-1,""))),)))
copy down

and format Custom as ###;-###;

HIH

 

[Bill Sharpe]

I have groupings of data in column A. Each group is obviously continguous.
The data grouping are separated by a single blank cell in the column. I
would like to sum the values in each group.

What I have:

A
1000
500
300
200

1000
400
100

1000
1000

What I'd like:

A B
1000
500
300
200
2000
1000
400
100
1500
1000
1000
2000

thanks!

Search Excel help for "subtotals."

Bill

 

[Doorman]

I am trying to find a solution to the same problem as the original poster. I
tried Jareks' solution (I want to avoid VBA) but this seemed to provide a
standard subtotal as shown:

A B

1000
500
300
200
2000
1000
400
100
3500
1000
1000
5500

I want the sum of each block:

A B

1000
500
300
200
2000
1000
400
100
1500
1000
1000
2000

My contraints are that A1 is blank, and the gaps between each block can be
more than one cell.

Are you able to help?

Thanks

 

[Roger Govier]

Hi

Try
=IF(ISBLANK(A1),"",IF(ISBLANK(A2),SUM($A$1:A2)-SUM($B$1:B1),""))

 

[Doorman]

Roger,

That worked fantastically. Thank you for your speedy response.