title: Excel formula to sum non-overlapping ranges of cells?
body: <p>Say I have numbers in A1 to A20 and I wanted to sum non-overlapping ranges of 5 cells in column A and store the results in cells in column E, it would look something like this (if the formulas were typed into each E column cell manually):</p>
  
  <pre><code>E1 = SUM(A1:A5)
  E2 = SUM(A6:A10)
  E3 = SUM(A11:A15)
  E4 = SUM(A16:A20)
  </code></pre>
  
  <p>However, I don't want to type the formulas into E cells manually. I want to be able to select the formula in E1 and drag it down to E4, whilst maintain the non-overlapping ranges of 5 cells in A! Excel does not give me this behavior by default, it does this instead:</p>
  
  <pre><code>E1 = SUM(A1:A5)
  E2 = SUM(A2:A6)
  E2 = SUM(A3:A7)
  E4 = SUM(A4:A8)
  </code></pre>
  
  <p>See how the ranges of 5 cells in each SUM() function overlap? e.g. A1:A5 and A2:A6. That's not what I want.</p>
  
  <p>So, what is a formula that would enable me to do this? Basically, the following pseudocode would work, but I can't seem to implement anything like it in Excel:</p>
  
  <pre><code>SUM(CELL(COLUMN, (CURRENT_ROW - 1) * 5 + 1):CELL(COLUMN, (CURRENT_ROW - 1) * 5 + 5))
  </code></pre>
  
  <p>For example, for E2, CURRENT_ROW = 2, meaning it would look like this:</p>
  
  <pre><code>SUM(CELL(A, (2 - 1) * 5 + 1):CELL(A, (2 - 1) * 5 + 5))
  = SUM(CELL(A, 6):CELL(A, 10))
  </code></pre>
  
  <p>This pseudocode assumes CELL has the method signature CELL(row, column).</p>