1

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.

  • 2
    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
2

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

2

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

1

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

SUM
OFFSET
INDIRECT
ADDRESS
ROW
COLUMN

0

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
0

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

enter image description here

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
0

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.

enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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