Copying down sum formula with non equal ranges

[jbenfleming]

I have two columns, one with dates and one with values. Such as:

[TABLE="width: 100"]
<tbody>[TR]
[TD]8/4/19[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8/5/19[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]8/6/19[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]8/7/19[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]8/8/19[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]8/9/19[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]8/10/19[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]8/11/19[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]8/12/19[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]8/13/19[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]8/14/19[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]8/15/19[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]8/16/19[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]8/17/19[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

In a separate cell I want to sum up the first week, i.e. =sum(b1:b7). I want to fill that formula down one row to capture the second week. But of course if I fill down one row it will change the formula to =sum(b2:b8) rather than my desired value, =sum(b8:b14). Any ideas on how to approach this?

 

[jbenfleming]

FYI,

I know I could accomplish this with VBA, but I am considering that a last case scenario.

 

[JoeMo]

Excel Workbook
	A	B	C
1	8/4/2019	1	29
2	8/5/2019	5	24
3	8/6/2019	4
4	8/7/2019	4
5	8/8/2019	7
6	8/9/2019	4
7	8/10/2019	4
8	8/11/2019	4
9	8/12/2019	3
10	8/13/2019	4
11	8/14/2019	3
12	8/15/2019	2
13	8/16/2019	4
14	8/17/2019	4
Sheet2

 

[jbenfleming]

Perfect. Thank you!

 

[JoeMo]

Perfect. Thank you!

You are welcome - thanks for the reply.