0

I've made an excel document from MS2013 (xlsx) that computes all numbers above it, because i want it to dynamically compute and adjust its own formula as i add more rows above it. But when i import this on google spreadsheets, the formula doesn't work anymore. here is the sample and formula

  A  B  C  D
1 3  3  2  2
2 4  3  4  5
3 5  6  4  3
4
5 12 12 10 10

the formula for A5 is =SUM(A1:OFFSET(A5,-1,0)) when i add 1 more row above A5 the formula adjusts accordingly to the formula's current position. What's the correct formula for google spreadsheet?

NewBorn
  • 5
  • 3
  • found answer here http://stackoverflow.com/questions/18962953/google-spreadsheet-sum-which-always-ends-on-the-cell-above – NewBorn Mar 06 '14 at 00:27

2 Answers2

0

I propose you this formula (it's certainly not the better solution, nevertheless it's a solution):

=sum(indirect("A1:"&ADDRESS(ROW()-1;COLUMN())))
Harold
  • 3,297
  • 1
  • 18
  • 26
  • Is there a way to get it to work if there are rows inserted above the range? With this formula it won't shift. Edit: http://stackoverflow.com/questions/18962953/google-spreadsheet-sum-which-always-ends-on-the-cell-above – Joe Aug 20 '15 at 20:53
-1

You can use this in your example:

=SUM(A1:A4)

If you start adding extra rows, then they will be automatically accounted for.

Jacob Jan Tuinstra
  • 1,197
  • 3
  • 19
  • 50