# 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