Hlookup a week number and sum from that point until week 52

[Katsimps]

Hi, I'm struggling to find the right formula to add up the sales from a certain week number (variable by row) until week 52

Assume sum of weeks = D2, I've manually summed the weeks from and to (in this example up to week 10), however I have over 600 rows of data that differ by row.

I hope I've requested correctly and apolgoies if not.

[TABLE="width: 1025"]
<colgroup><col><col span="3"><col><col span="10"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Completion Date[/TD]
[TD]Week Number From[/TD]
[TD]To[/TD]
[TD]Sum of Weeks[/TD]
[TD]Store Name[/TD]
[TD="align: right"]06-Jan[/TD]
[TD="align: right"]13-Jan[/TD]
[TD="align: right"]20-Jan[/TD]
[TD="align: right"]27-Jan[/TD]
[TD="align: right"]03-Feb[/TD]
[TD="align: right"]10-Feb[/TD]
[TD="align: right"]17-Feb[/TD]
[TD="align: right"]24-Feb[/TD]
[TD="align: right"]03-Mar[/TD]
[TD="align: right"]10-Mar[/TD]
[/TR]
[TR]
[TD="align: right"]13/01/2018[/TD]
[TD]2[/TD]
[TD]10[/TD]
[TD]1,684 [/TD]
[TD]Store A[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]210[/TD]
[TD="align: right"]219[/TD]
[TD="align: right"]315[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]480[/TD]
[TD="align: right"]157[/TD]
[/TR]
[TR]
[TD="align: right"]10/03/2018[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]497 [/TD]
[TD]Store B[/TD]
[TD="align: right"]332[/TD]
[TD="align: right"]383[/TD]
[TD="align: right"]401[/TD]
[TD="align: right"]299[/TD]
[TD="align: right"]152[/TD]
[TD="align: right"]355[/TD]
[TD="align: right"]224[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]156[/TD]
[TD="align: right"]497[/TD]
[/TR]
[TR]
[TD="align: right"]03/02/2018[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]1,853 [/TD]
[TD]Store C[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]356[/TD]
[TD="align: right"]330[/TD]
[TD="align: right"]258[/TD]
[TD="align: right"]398[/TD]
[TD="align: right"]164[/TD]
[TD="align: right"]227[/TD]
[TD="align: right"]476[/TD]
[/TR]
[TR]
[TD="align: right"]18/02/2018[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD]812 [/TD]
[TD]Store D[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]142[/TD]
[TD="align: right"]162[/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]124[/TD]
[TD="align: right"]364[/TD]
[TD="align: right"]176[/TD]
[TD="align: right"]272[/TD]
[/TR]
</tbody>[/TABLE]

 

[szita2000]

Hi.

Welcome to the forum.

Try to put this in to Cell P3 then drag it down

=IF($C3=$B3,OFFSET($F3,0,C$3-1),SUM(OFFSET($F3,0,$B3-1,1,($C3-$B3)+1)))

If it works you can add it in to Sum of Weeks column.

 

[Katsimps]

Hi.

Welcome to the forum.

Try to put this in to Cell P3 then drag it down

=IF($C3=$B3,OFFSET($F3,0,C$3-1),SUM(OFFSET($F3,0,$B3-1,1,($C3-$B3)+1)))

If it works you can add it in to Sum of Weeks column.

Thank you so much, this works!

 

[Katsimps]

Thank you so much, this works!

 

[szita2000]

You welcome.