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