Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
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
@jmax please add that solution as an answer so Xeon06 can mark this question answered – Raystafarian Feb 13 '12 at 15:41
@Raystafarian: ok. Done :) – JMax Feb 13 '12 at 15:47
up vote 2 down vote accepted

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) .

share|improve this answer

This will do:


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 ",".

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.