Create a formula that sums up values between equally spaced cells

Question

I am trying to make my cells follow a continuous incremental pattern so that I can drag it down and it applies to all cells. I am creating the calculation in one sheet, but the data is in other sheet.

For example:

I would like one cell to be: = SUM('RAWDATA'!A2:A8) and then the cell underneath it to go =SUM('RAWDATA'!A9:A15) aka, increasing incrementally by 7 based on the last value in the range in the cell before it. So basically the cell underneath it would start at A16 and end A22. What keeps happening is the values go A2:A8 then A3:A9 :( when I drag the values so that it can apply to all cells underneath it.

I am doing this because I would like to sum the values between several set of dates. So summing the total units sold between 06/11 and 06/18, then 6/19 to 6/25 so on so forth.

Anyone have any suggestions? Thank you for your help! :)

Answer 1

See if below approach helps. It uses 2 helper cells though.

See the below screenshot.

Range is in Helper Cells G1 & H1 say 2 & 8 as you desire.

Now in C2 put the following formula and Drag it down up to intended rows.

=SUM(INDEX(A:A,$G$1+7*(ROW(A1)-1)):INDEX(A:A,$H$1+7*(ROW(A1)-1)))

It uses INDEX to return Cell Reference and SUM to wrap it to get desired result. Formula should work in any cell. The initial start & end reference is set in Helper Cells G1 & H1 though.

You can change it to Sheet reference as well like Sheet2!A:A but in my example for screenshot purposes it sums cells from range A:A from same sheet though.

---

Solution without Helper Cells, but then it hardcodes 2 & 8 as ROW reference.

=SUM(INDEX(A:A,ROW($A$2)+7*(ROW(A1)-1)):INDEX(A:A,ROW($A$8)+7*(ROW(A1)-1)))

---

Answer 2

You may try this also:

Formula in cell Ak2: =SUM(OFFSET(AI$2,(ROW(AL1)-1)*7,0,7,1))

N.B.

• You may adjust cell references as needed.