I want to produce a sum for every seven entries of Column A, Sheet1, displayed in Sheet2. The point in which I'm stuck is how to define the formula the the formula-drag-down produces this formula with 7-step increments.
My table in Sheet1 looks something like this
A
1 1:00
2 1:00
3 1:00
4 1:00
5 1:00
6 1:00
7 1:00
and in Sheet2 in the respective cell there should be an entry 7:00 which sums Sheet1.1-7 the next entry should sum Sheet1.8-14, the next Sheet1.15-21 and so forth, all by dragging down the formula...
I think my solution is somewhat linked to the OFFSET() function, but I got stuck somehow.
If you put the formula
=(ROW($A1)-1)*7
somewhere and drag down, then you get the serie 0, 7, 14, 21, ...
This is because the dragging down will adapt $A1 to $A2, $A3, ...
So
=SUM(OFFSET($Sheet1.$A$1,(ROW($A1)-1)*7,0,7,1))
will be the formula for your requirements.
Maybe you have to use semicolon ; as formula separator instead of comma ,.
=SUM(OFFSET($Sheet1.$A$1;(ROW($A1)-1)*7;0;7;1))
One Problem with this approach is that if you insert rows above row 1, then all the formulas which refers row 1 will be adapted. So =(ROW($A1)-1)*7 will be adapted to =(ROW($A2)-1)*7. This will impact the functionality of this approach. To avoid this we can use
=(ROWS($A$1:$A1)-1)*7
instead. With this, the adapting to =(ROWS($A$2:$A2)-1)*7 will not impact the functionality.