title: Google Spreadsheet sum which always ends on the cell above
body: <p>How to create a Google Spreadsheet sum() which always ends on the cell above, even when new cells are added? I have several such calculations to make on each single column so <a href="https://stackoverflow.com/questions/12223180/google-spreadsheet-sum-of-row-n-through-last-row">solutions like this</a> won't help.</p>
  
  <p>Example: </p>
  
  <blockquote>
    <p>On column B, I have several dynamic ranges which has to be summed. B1..B9 should be summed on B10, and B11..B19 should be summed on B20. I have tens such calculations to make. Every now and then, I add rows below the last summed row , and I want them to be added to the sum. I add a new row (call it 9.1) before row 10, and a new raw (let's call it 19.1) before row 20. I want B10 to contain the sum of B1 through B9.1 and B20 to contain the sum of B11:B19.1. </p>
  </blockquote>
  
  <p>On excel, I have the <a href="http://spreadsheets.about.com/od/excelslookupfunctions/ss/2013-04-20-excel-sum-offset-formula-tutorial.htm" rel="noreferrer">offset function</a> which does it like charm. But how to do it with google spreadsheet? I tried to use formulas like this:</p>
  
  <pre><code>=SUM(B1:INDIRECT(address(row()-1,column(),false)))   # Formula on B10
  =SUM(B11:INDIRECT(address(row()-1,column(),false)))  # Formula on B20
  </code></pre>
  
  <p>But on Google Spreadsheet, all it gives is a #name error.</p>
  
  <p>I wasted hours trying to find a solution, maybe someone can calp?
  Please advise</p>
  
  <p>Amnon</p>