Excel formula to sum non-overlapping ranges of cells?
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):
E1 = SUM(A1:A5)
E2 = SUM(A6:A10)
E3 = SUM(A11:A15)
E4 = SUM(A16:A20)
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:
E1 = SUM(A1:A5)
E2 = SUM(A2:A6)
E2 = SUM(A3:A7)
E4 = SUM(A4:A8)
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.
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:
SUM(CELL(COLUMN, (CURRENT_ROW - 1) * 5 + 1):CELL(COLUMN, (CURRENT_ROW - 1) * 5 + 5))
For example, for E2, CURRENT_ROW = 2, meaning it would look like this:
SUM(CELL(A, (2 - 1) * 5 + 1):CELL(A, (2 - 1) * 5 + 5))
= SUM(CELL(A, 6):CELL(A, 10))
This pseudocode assumes CELL has the method signature CELL(row, column).
Answer
The key to this is the OFFSET function. Offset takes a starting cell, and a number of rows, columns, and optional height and width to generate a reference to a cell/range on the fly. The trick would be to use other functions to generate the row offset and other parameters.
I've just knocked together something that seems to do what you want, I'll see if I can explain it...
Column A: integers (A1) 0, (A2) 1, (A3) 2,3,4,5 etc... this is an index for how many you're offsetting by, you could probably remove this using something like the ROW function) Column D (or any other arbitrary location): the numbers you're wanting to sum, starting at D1 - I actually just used (D1) 1, (D2) 2,3,4,5...
Column B is the one that's interesting to you, formula is:
=SUM(OFFSET(D$1,A1*5,0,5,1))
What it's doing: summing the range defined by: A block of cells starting (A1 * 5) cells down and 0 across from $D$1, which is 5 high by 1 wide. See edit, I left this in because you could put arbitrary numbers in the A cells and use this principle.
Hope that makes some kind of sense? Excel doesn't lend itself to text explanations!
Edit: Removed the cells A1..., remembered that ROW() allows self-references, which means you can do
=SUM(OFFSET(D$1,(ROW(B1)-1)*5,0,5,1))
Where this formula is in Cell B1 (pasted down), and your data is in Column D starting at 1.