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
|