0

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?

Here is a screen capture of what I am trying to achieve

Samuel Hulla
  • 6,617
  • 7
  • 36
  • 70
Pete
  • 23
  • 2
  • 8

1 Answers1

0

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)

enter image description here

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Thank you, that was very helpful, I applied this but ended up going with a solution using the Indirect function. I made a list of all the cells I need for each Sunday and Saturday then used a =Sum(Indirect(...):Indirect(...)) function to get what I wanted. My solution is not very elegant but does work (I'm on quite a learning curve here). Thanks once again Ron for your help. – Pete Aug 02 '18 at 12:08
  • @Pete One problem with using `INDIRECT` is that it is a volatile function. See [Volatile Excel Functions](http://www.decisionmodels.com/calcsecretsi.htm). For the reasons mentioned in the citation, they can significantly slow down your worksheet. They do have some advantages, in certain situations, but it is good practice to try to avoid them unless necessary. In a small worksheet, without much else going on, their impact may be minimal. – Ron Rosenfeld Aug 02 '18 at 12:14