[Dropped] Sum all numbers above current cell

[Digika]

Edit: Split from [Solved] How to sum all numbers above current cell because that topic is solved and cannot accept a new question from a new person. You should open your own topic, Digika, for this subject since your situation "more dynamic with dynamic offset" is different. -- MrProgrammer, forum moderator

How can I make it more dynamic with dynamic offset? Say I have column CI30-CI40 and CI41 will be SUM, however, some rows might be deleted and now I have CI30-CI-37, and CI38 is the sum - so basically what I want is to calculate how many row are there from [$current-CI30] and then do the sum. So i need to go up from dynamic cell to fixed cell and summarize.

[Villeroy]

ay I have column CI30-CI40 and CI41 will be SUM, however, some rows might be deleted and now I have CI30-CI-37, a

If you actually delete the row, the formula adjusts automatically.
Same when you actually insert rows.

[Lupp]

If you use dedicated columns where only entries of the same "kind" (semantically defined group here) are entered (or calculated from entries in different columns), you should simply place totals or related calculations ABOVE the data.
Thats better for the view (fix the rows), and much better for the structure.
If you calculate =SUM(CJ$2:CJ$1048576) many possible problems simply vanish, and the internal working of Calc will apply very efficient means to avoid "millions of unnecessary calculations". Simply try it. (Calc isn't a maternal-school exercise-book.)

Of course, there are also ways to calculate the parameters for OFFSET(), and there are settings to allow for automatic expansion of ranges (under a certain condition), But why do it the complicated way?

Intermediary partial evaluation (partial sums, averages,... "subtotals") breaking the semantic unity is evil paper-think.

[Digika]

ay I have column CI30-CI40 and CI41 will be SUM, however, some rows might be deleted and now I have CI30-CI-37, a

If you actually delete the row, the formula adjusts automatically.
Same when you actually insert rows.

It does not, I've just tested. Also, your formula starts from top, I need reverse order, from bottom of $CURRENT_CELL_FORUMLA_IS_IN to specific top fixed.

[robleyd]

You may need to make a change in your settings. Tools | Options | OpenOffice Calc | General

[Villeroy]

You may need to make a change in your settings. Tools | Options | OpenOffice Calc | General

Without that option being checked:
Insertion at the top row moves the reference down. SUM(A1:A9) becomes SUM(A2:A10).
Insertion below the top row expands the reference. SUM(A1:A9) becomes SUM(A1:A10).
Insertion below the referenced range does nothing. SUM(A1:A9) remains the same when the insertion is in row #10.

With that option being checked:
Insertion anywhere within the referenced range or directly below expands the reference. SUM(A1:A9) becomes SUM(A1:A10). Likewise with columns.
This can be annoying in some cases which is why we can turn it off. You want this setting for list keeping in a spreadsheet.