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

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`
{"ft_lang_label":"__label__en","ft_lang_prob":0.82232016,"math_prob":0.9778767,"size":806,"snap":"2021-43-2021-49","text_gpt3_token_len":191,"char_repetition_ratio":0.12593517,"word_repetition_ratio":0.0,"special_character_ratio":0.26674938,"punctuation_ratio":0.15294118,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9956849,"pos_list":[0,1,2,3,4,5,6],"im_url_duplicate_count":[null,4,null,4,null,4,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2021-10-19T07:07:22Z\",\"WARC-Record-ID\":\"<urn:uuid:f865c092-c423-4555-8d0d-aac90be03167>\",\"Content-Length\":\"217181\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:e22f3165-359b-4d75-a34e-19bb50faaf93>\",\"WARC-Concurrent-To\":\"<urn:uuid:dc830b4a-4818-4ff7-8ce7-2c95e4d29a68>\",\"WARC-IP-Address\":\"151.101.193.69\",\"WARC-Target-URI\":\"https://webapps.stackexchange.com/questions/23593/sum-of-the-values-of-the-10-cells-above-me\",\"WARC-Payload-Digest\":\"sha1:O6V63BXLGUJXNUSIQ6GRIY6QWSEFETYB\",\"WARC-Block-Digest\":\"sha1:77XHIDO44XBQKVQFQKE4VJISJALKFGMG\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2021/CC-MAIN-2021-43/CC-MAIN-2021-43_segments_1634323585242.44_warc_CC-MAIN-20211019043325-20211019073325-00151.warc.gz\"}"}