# Sum of the values of the 10 cells above me?

I'd like a formula for Google Docs spreadsheets that will return the sum of the values of the 10 cells above the current cell.

That is, if I'm on C31, I want the equivalent of `=SUM(C20:C30)`. However, I want something that will work independently of the current cell.

-
if you copy-paste your formula, this will "move" the formula (even if you copy it to any other cell) – JMax Feb 6 '12 at 9:56
@JMax Indeed, I realized this a while later. Thanks for the tip! – Xeon06 Feb 6 '12 at 14:56
@Raystafarian: ok. Done :) – JMax Feb 13 '12 at 15:47

As discussed in the comments, if you copy-paste your formula, this will "move" the formula (even if you copy it to any other cell).

This is the same behavior as Excel. Note that you can use the `\$` too to keep either the column (`=\$A1`) or the row (`=A\$1`) or both (`=\$A\$1`) .

-

This will do:

``````=SUM(INDIRECT(ADDRESS(ROW()-11;COLUMN();4) &":"& ADDRESS(ROW()-1;COLUMN();4)))
``````

Just paste it any cell you like and it will sum 10 cells above it, with no changes to the value.

The `ADDRESS` function returns a string with the name reference of a cell. We join two references with a ":" between them, giving us a string like "B5:B15". We them feed the `INDIRECT` function with this string and it will convert the string to a internal reference. Them we feed this reference to the `SUM` function and there you go.

Beware of the ";" chars separating the parameters of the function. I'm using Brazilian Portuguese spreadsheet and the separator is the ";", but it may vary depending on the locale of the sheet. In US I think they use commas (","), so if it gives you any error, try switching ";" for ",".

-