Summing dynamic range
 

Let imagine this situation:
I have column with set of ranges and I need to sumarise every particular
range.

for example:

D4: 9
D5: 5
D6: 3
D7: =SUM(D4:D6)
D8:
D9: 1
D10: 2
D11: 3
D12: =SUM(D9:D11)
D13:
D14: 1
D15: 1
D16: 1
D17: 1
D18: 1
D19: 1
D20: 1
D21: =SUM(D14:D20)
etc ....



My Idea is have in every "Sum cell" the same formula. Every single range can
vary in mumber of rows.
In VBA I can use .CurrentRegion.Rows.Count. Is something similar possible in
sheet formulas ?

Thanks in advance to all !
Vlado

Summing dynamic range
 

How do you identify which rows to sum?

"Vlado Sveda" wrote:

Let imagine this situation:
I have column with set of ranges and I need to sumarise every particular
range.

for example:

D4: 9
D5: 5
D6: 3
D7: =SUM(D4:D6)
D8:
D9: 1
D10: 2
D11: 3
D12: =SUM(D9:D11)
D13:
D14: 1
D15: 1
D16: 1
D17: 1
D18: 1
D19: 1
D20: 1
D21: =SUM(D14:D20)
etc ....



My Idea is have in every "Sum cell" the same formula. Every single range can
vary in mumber of rows.
In VBA I can use .CurrentRegion.Rows.Count. Is something similar possible in
sheet formulas ?

Thanks in advance to all !
Vlado

Summing dynamic range
 

That's the problem I need to solve. I need to sum all cells above the "Sum
cell" in continuous range - as described in my example.

"bj" wrote:

How do you identify which rows to sum?

"Vlado Sveda" wrote:

Let imagine this situation:
I have column with set of ranges and I need to sumarise every particular
range.

for example:

D4: 9
D5: 5
D6: 3
D7: =SUM(D4:D6)
D8:
D9: 1
D10: 2
D11: 3
D12: =SUM(D9:D11)
D13:
D14: 1
D15: 1
D16: 1
D17: 1
D18: 1
D19: 1
D20: 1
D21: =SUM(D14:D20)
etc ....



My Idea is have in every "Sum cell" the same formula. Every single range can
vary in mumber of rows.
In VBA I can use .CurrentRegion.Rows.Count. Is something similar possible in
sheet formulas ?

Thanks in advance to all !
Vlado

Summing dynamic range
 

Vlado,

The following is not the most elegant solution I can think of, but it
works, provided that there are still blank cells between the blocks,
after your summation formula is inserted. The following *array*
formula is placed directly below a contiguous group of cells in column
A, with data starting from A1:

=SUM(INDIRECT(ADDRESS(ROW()-1,COLUMN())&":"&ADDRESS(MAX(IF(A
$1:A12="",ROW(A$1:A12)))+1,COLUMN())))

As it is an array formula, commit with Shift+Ctrl+Enter.

HTH
Kostis Vezerides

On Oct 11, 2:03 pm, Vlado Sveda
wrote:
Let imagine this situation:
I have column with set of ranges and I need to sumarise every particular
range.

for example:

D4: 9
D5: 5
D6: 3
D7: =SUM(D4:D6)
D8:
D9: 1
D10: 2
D11: 3
D12: =SUM(D9:D11)
D13:
D14: 1
D15: 1
D16: 1
D17: 1
D18: 1
D19: 1
D20: 1
D21: =SUM(D14:D20)
etc ....

My Idea is have in every "Sum cell" the same formula. Every single range can
vary in mumber of rows.
In VBA I can use .CurrentRegion.Rows.Count. Is something similar possible in
sheet formulas ?

Thanks in advance to all !
Vlado

Summing dynamic range
 

Hi
With your values to be summed in column D, enter in E2
=IF(D2<"","",SUM($D$1:D1)-SUM($E$1:E1))
copy down column E as far as required
--
Regards
Roger Govier



"Vlado Sveda" wrote in message
...
That's the problem I need to solve. I need to sum all cells above the "Sum
cell" in continuous range - as described in my example.

"bj" wrote:

How do you identify which rows to sum?

"Vlado Sveda" wrote:

Let imagine this situation:
I have column with set of ranges and I need to sumarise every
particular
range.

for example:

D4: 9
D5: 5
D6: 3
D7: =SUM(D4:D6)
D8:
D9: 1
D10: 2
D11: 3
D12: =SUM(D9:D11)
D13:
D14: 1
D15: 1
D16: 1
D17: 1
D18: 1
D19: 1
D20: 1
D21: =SUM(D14:D20)
etc ....



My Idea is have in every "Sum cell" the same formula. Every single
range can
vary in mumber of rows.
In VBA I can use .CurrentRegion.Rows.Count. Is something similar
possible in
sheet formulas ?

Thanks in advance to all !
Vlado

Summing dynamic range
 

select the cell you want the sum to be in go up to your workbar and pust the
button with the sigma on it. I "think" it will do exactly what you want.

"Vlado Sveda" wrote:

That's the problem I need to solve. I need to sum all cells above the "Sum
cell" in continuous range - as described in my example.

"bj" wrote:

How do you identify which rows to sum?

"Vlado Sveda" wrote:

Let imagine this situation:
I have column with set of ranges and I need to sumarise every particular
range.

for example:

D4: 9
D5: 5
D6: 3
D7: =SUM(D4:D6)
D8:
D9: 1
D10: 2
D11: 3
D12: =SUM(D9:D11)
D13:
D14: 1
D15: 1
D16: 1
D17: 1
D18: 1
D19: 1
D20: 1
D21: =SUM(D14:D20)
etc ....



My Idea is have in every "Sum cell" the same formula. Every single range can
vary in mumber of rows.
In VBA I can use .CurrentRegion.Rows.Count. Is something similar possible in
sheet formulas ?

Thanks in advance to all !
Vlado

Summing dynamic range
 

Hi Kostis,

your code works fine, but in my case I don't know starting and ending cell
of range that I want summarise. I need substitute A$1:A12 with "something
flowing". In my example (see bellow) I need SUM in D7, SUM in D12, SUM in
D21, ... but these addresses are "flowing" because those ranges (D4:D6,
D9:D11, D14:D20, ...) are dynamic in their sizes. These ranges could be
D4:D20, D23:D24, D27:D1234, ... or whatever else ... and all these ranges are
in one column.
So that is the reason I need "summing dynamic ranges".

Regards
Vlado

"vezerid" wrote:

Vlado,

The following is not the most elegant solution I can think of, but it
works, provided that there are still blank cells between the blocks,
after your summation formula is inserted. The following *array*
formula is placed directly below a contiguous group of cells in column
A, with data starting from A1:

=SUM(INDIRECT(ADDRESS(ROW()-1,COLUMN())&":"&ADDRESS(MAX(IF(A
$1:A12="",ROW(A$1:A12)))+1,COLUMN())))

As it is an array formula, commit with Shift+Ctrl+Enter.

HTH
Kostis Vezerides

On Oct 11, 2:03 pm, Vlado Sveda
wrote:
Let imagine this situation:
I have column with set of ranges and I need to sumarise every particular
range.

for example:

D4: 9
D5: 5
D6: 3
D7: =SUM(D4:D6)
D8:
D9: 1
D10: 2
D11: 3
D12: =SUM(D9:D11)
D13:
D14: 1
D15: 1
D16: 1
D17: 1
D18: 1
D19: 1
D20: 1
D21: =SUM(D14:D20)
etc ....

My Idea is have in every "Sum cell" the same formula. Every single range can
vary in mumber of rows.
In VBA I can use .CurrentRegion.Rows.Count. Is something similar possible in
sheet formulas ?

Thanks in advance to all !
Vlado

Summing dynamic range
 

Vlado,

First of all, I used A$1:Ax so that the formula can be copied to any
column and always look from the top cell of that column. Also, the
formulas are indeed "dynamic" in that, once placed below a contiguous
block of cells, they will produce the sum of the block.

Originally I thought you already had the numbers and were seeking
formulas to insert in the blanks. I am a bit perplexed after your
answer. How are these numbers coming, from some VBA or query? If we
have a column which is always changing then you might need a VBA
solution, to implant the sum automatically under each block.

Please try to be more specific as to your requirements or email me a
copy of your file with annotations.

Regards,
Kostis

On Oct 12, 9:31 am, Vlado Sveda
wrote:
Hi Kostis,

your code works fine, but in my case I don't know starting and ending cell
of range that I want summarise. I need substitute A$1:A12 with "something
flowing". In my example (see bellow) I need SUM in D7, SUM in D12, SUM in
D21, ... but these addresses are "flowing" because those ranges (D4:D6,
D9:D11, D14:D20, ...) are dynamic in their sizes. These ranges could be
D4:D20, D23:D24, D27:D1234, ... or whatever else ... and all these ranges are
in one column.
So that is the reason I need "summing dynamic ranges".

Regards
Vlado

"vezerid" wrote:
Vlado,

The following is not the most elegant solution I can think of, but it
works, provided that there are still blank cells between the blocks,
after your summation formula is inserted. The following *array*
formula is placed directly below a contiguous group of cells in column
A, with data starting from A1:

=SUM(INDIRECT(ADDRESS(ROW()-1,COLUMN())&":"&ADDRESS(MAX(IF(A
$1:A12="",ROW(A$1:A12)))+1,COLUMN())))

As it is an array formula, commit with Shift+Ctrl+Enter.

HTH
Kostis Vezerides

On Oct 11, 2:03 pm, Vlado Sveda
wrote:
Let imagine this situation:
I have column with set of ranges and I need to sumarise every particular
range.

for example:

D4: 9
D5: 5
D6: 3
D7: =SUM(D4:D6)
D8:
D9: 1
D10: 2
D11: 3
D12: =SUM(D9:D11)
D13:
D14: 1
D15: 1
D16: 1
D17: 1
D18: 1
D19: 1
D20: 1
D21: =SUM(D14:D20)
etc ....

My Idea is have in every "Sum cell" the same formula. Every single range can
vary in mumber of rows.
In VBA I can use .CurrentRegion.Rows.Count. Is something similar possible in
sheet formulas ?

Thanks in advance to all !
Vlado