In Excel how can I sum all the numbers above the current cell?

The functions ROW() and COLUMN() will give the current cell's row and column. Use them in the ADDRESS() function to create a string representing the range from the top of the current column to the row above the total. Then use the INDIRECT() function to turn that string into a real range to give to the SUM() function. The formula for the total cell would then be:

=SUM(INDIRECT(ADDRESS(1,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())))

Put that in any cell in a spreadsheet and it will produce the sum of all the numbers in the column above that cell.

This answer is specific to Libra Office but should work for Excel as well.

Basically, if we want to explicitly mention the row/column to be frozen, we should mention the $ with that row/column.

*There is an inbuilt algo as well such that when you copy the formula to other cells, Libra Office will auto-determine how it should change the formula values.

So, lets assume you have to add all rows G1:GN => where N is the current row number. For this you want to fix the row of first value and keep other values as. So the formula will be :

=SUM(G$1:G1) -> for 1st row and then you can copy-paste the formula to other cells.

Libra Office will automatically keep G1 as first value and dynamically change 2nd value to G2, G3 and so on for every cell.

Actually, you can do what you want with plain =SUM()

Assuming your Excel sheet has the following design:

\|  A  |  B  |
-+-----+-----+
1|  1  |     |
2|  2  |     |
3|  3  |     |
4|  4  |     |
5|  5  |     |
6|     |     |
7|  15 |Total|

Assumng A7 is =SUM(A1:A5), you can add more rows as you please, as Excel will expand the =SUM's range accordingly.

Assume now that I select row 2, 3 and 4, and I insert rows. The result will be the following:

 \|  A  |  B  |
--+-----+-----+
 1|  1  |     |
 2|     |     |
 3|     |     |
 4|     |     |
 5|  2  |     |
 6|  3  |     |
 7|  4  |     |
 8|  5  |     |
 9|     |     |
10|  15 |Total|

A10 equals =SUM(A1:A8). As such, you can now insert new numbers at will.

ok, Indirect() is volatile... so as your sheet grows bigger, it will only get slower. If it suites you, you'd better use:

=Sum($A1:A1)

when you drag this formula down, you will always have a range from A1 till the current (or previous etc) cell. No Volatile, very fast, much much more simple!

Editing after 7years to add one small piece for completeness of unorthodox and not 100% answers! Use cyclic reference! So you add C1 to C5 at C6, then simply write at c6 = sum(c$1:c6)-c6 press enter choose OK (at the warning of cyclic reference) go to file / options / formulas / calculation options check [iterative calculations] set [maximum iterations] = 1 now you are done!

++ if follows you no matter what... ++ you can use cyclic formulas! :D:D ---- if you accidentally write cyclic formula you will not be notified :((