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

I'd like a formula for Google Sheets that will return the sum of the values of the ten 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
• @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

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

A simplified version of @diego nunes' A:

``````=sum(offset(indirect(ADDRESS(ROW(),COLUMN())),-10,,10))
``````

This can be dragged around and work anywhere there are ten or more rows above.

However, as suggested by @JMax something like `=sum(a1:a10)` if written in A11 can be copy and pasted and (other than in the top ten rows) will automatically adjust to suit, so copied from A11 to say C14 would become:

``````  =sum(c4:c13)
``````

(drag it however and it stays `=sum(a1:a10)`).

try:

``=SUM(OFFSET(C2; MAX(ARRAYFORMULA(ROW(C2:C30)*--(C2:C30<>"")))-11; ; 10))``

again based on a false calculation where `ROW` number gets multiplied by the cell that isn't empty `C2:C30<>""` in order to find out where is the last entry in a given range `C2:C30`. then `ARRAYFORMULA` generates list of values which are filtered by `MAX` to catch all errors (like `25 * x`) and then reverse `OFFSET` is used from a starting point/cell C2 where is the beginning of the list, which is then restricted by last 10 values with `-11, , 10` and passed into `SUM` formula. as seen from the image, this solution takes the sum from:

• column B sums numbers: `6, 1, 5, 7, 8, 7, 6, 5, 9, 45`
• column C sums numbers: `5, x, 3, 2, x, 5, 7, 9, 8, 5`
• column D sums numbers: `1, 8, 1, empty cell, 1, 2, 2, empty cell, 2, 1`

try:

``=SUM(QUERY(SORT(C2:C30; ROW(C2:C30)*ISNUMBER(C2:C30); 0); "select * limit 10"))``

this is basically based on a false calculation where `ROW` number gets multiplied by the number (`ISNUMBER`) in the cell to find out where is the last number in a given range `C2:C30`. an output is then mirrored with `SORT` so last value becomes first from the top and then `QUERY` takes it all and outputs only `10` rows/values that are passed into `SUM` as seen from the image, this solution takes the sum from:

• column B sums numbers: `6, 1, 5, 7, 8, 7, 6, 5, 9, 45`
• column C sums numbers: `5, 3, 2, 5, 7, 9, 8, 5, 4, 1`
• column D sums numbers: `1, 8, 1, 1, 2, 2, 2, 1, 9, 2`

try:

``````=SUM(INDEX(C2:C30; MATCH(99999; C2:C30)-9):
INDEX(C2:C30; MATCH(99999; C2:C30)))``````

this solution is based on a search for fake number `99999` in the range `C2:C30` and unless that specific number is not provided, there is no `MATCH` so 2 `INDEX` lists are generated while one of them is slightly offset by two rows `-9` which kind off negates all the values leaving only last 10 cells to be calculated by `SUM` formula. as seen from the image, this solution takes the average from:

• column B sums numbers: `6, 1, 5, 7, 8, 7, 6, 5, 9, 45`
• column C sums numbers: `5, x, 3, 2, x, 5, 7, 9, 8, 5`
• column D sums numbers: `1, 8, 1, empty cell, 1, 2, 2, empty cell, 2, 1`