Summing dynamic range

[Vlado Sveda]

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(D46)
D8:
D9: 1
D10: 2
D11: 3
D12: =SUM(D911)
D13:
D14: 1
D15: 1
D16: 1
D17: 1
D18: 1
D19: 1
D20: 1
D21: =SUM(D1420)
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

 

[bj]

How do you identify which rows to sum?

 

[Vlado Sveda]

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.

 

[vezerid]

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

 

[Roger Govier]

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

 

[bj]

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]

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 (D46,
D911, D1420, ...) are dynamic in their sizes. These ranges could be
D420, D2324, D271234, ... or whatever else ... and all these ranges are
in one column.
So that is the reason I need "summing dynamic ranges".

Regards
Vlado

 

[vezerid]

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