Summing Contiguous Values in a Column?
 

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!

Summing Contiguous Values in a Column?
 

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 he
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.)

Confused_in_Houston wrote:

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

Summing Contiguous Values in a Column?
 

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


On 17 Mar, 14:50, Confused_in_Houston
wrote:
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!

Summing Contiguous Values in a Column?
 

Confused_in_Houston wrote:
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

Summing Contiguous Values in a Column?
 

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


"Jarek Kujawa" wrote:

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


On 17 Mar, 14:50, Confused_in_Houston
wrote:
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!

Summing Contiguous Values in a Column?
 

Hi

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

--
Regards
Roger Govier

Doorman wrote:
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


"Jarek Kujawa" wrote:

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


On 17 Mar, 14:50, Confused_in_Houston
wrote:
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!

Summing Contiguous Values in a Column?
 

Roger,

That worked fantastically. Thank you for your speedy response.

"Roger Govier" wrote:

Hi

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

--
Regards
Roger Govier

Doorman wrote:
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


"Jarek Kujawa" wrote:

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


On 17 Mar, 14:50, Confused_in_Houston
wrote:
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!

.