Copying formulas to include cell references seven rows down

[Dbroek]

In the attached file, inside cell J5, I have the formula =SUM(D11:D17), which reflects the value of 40. In cell J6, I would like the formula SUM(D18:D24) or an equivalent be inserted to reflect the value of 19. In cell J7, I would like the formula SUM(D25:D31) or an equivalent be inserted to reflect the value of 26. And, so on, so on, down column J. The idea is to create a dynamic table that will keep copying the cell references in the J column down as new values are entered into the B through F columns.

Can this actually be done? I tried several different formula combination using concatenate functions but these don't work in SUM formulas.

[Punnam]

Hi,

Kindly check this.

Punnam

[Dbroek]

Punnam, thank you for your response. I added more data to the table and the formulas you added did not work. I'm not familiar with how these should work or what I must do to get them to work again.

See the updated worksheet to see what I mean...

[Dbroek]

Opps, just realized that the range only extended to row 59. I can adjust that but am wondering how can I make this more dynamic or able to expand as more data is added?

This is a great piece of work. Very worthwhile addition, thank you!

[Fluff13]

How about
=SUMIFS(Table4[Correct],Table4[Date],">="&I4,Table4[Date],"<="&I4+7)

[Punnam]

How about
=SUMIFS(Table4[Correct],Table4[Date],">="&I4,Table4[Date],"<="&I4+7)

Fluff13 that was a very nice observation I never notice that column.

Punnam

[Fluff13]

Thanks for the that & the Rep.

[Dbroek]

Thank you Gents. Both are answers that can be applied to my project, depending in which direction I am able to go. I am grateful to know of this.
This will be used for a good cause to benefit the special education field.

[Dbroek]

I have a question about why Fluff13's formula caused the cell J13 to jump to a Correct sum of 71 when it should have summed to 40?
For some unknown reason, it summed up two weeks worth of data (the week of 3/5/17 and the week of 3/12/17) when it should have summed just the week of 3/5/17.
Did this happen at your end as well?

[Fluff13]

Because on row 76 your dates jump backwards from 13th March to 7th March

[Dbroek]

Aha, I see that now. Thanks for pointing that out.
Need to remind myself to check often to make sure my data is clean and accurately placed.

[Fluff13]

You're welcome.