MS Excel 2016 - Weekly Totals Click and Drag

Question

I am trying to create a table that totals the number of hours for all weeks.

I don't want the totals to appear on each 7th line but one after the other. The formula I am using is simple but when I use click and drag the autocomplete function is not adding the totals by 7 (for the week), rather is starts to repeat itself repeating the same three sums again and again.

What should I do to be able to use click and drag to get the weeks to populate my table?

Answer 1

I don't think you can do that with auto complete. You can devise formulas that will do the same thing.

For a formula that is tied to the particular row numbers you mention:

=SUM(INDEX($C:$C,2+(ROWS($1:1)-1)*7):INDEX($C:$C,2+ROWS($1:1)*7))

But, since you are working with dates, and you might have a missing date, or possibly two rows with the same date, in your data, the following might be a better approach:

=SUMIFS($C:$C,$B:$B,">="&$B$2+(ROWS($1:1)-1)*7,$B:$B,"<"&$B$2+ROWS($1:1)*7)

In each case, we advance the starting and ending locations (or dates) by a factor of seven (7).

If you test the part:

(ROWS($1:1)-1) * 7

you will see that it will sequentially add {0,7,14,...} to the base as you fill down.

You could also solve this with a Pivot table, grouping the dates by weeks (actually by seven days in the grouping dialog)