formula to caculate last 6 weeks from current week & sum below cells

[ALAMZEB]

Hi Guys


Can I get a formula that calculate last 6 weeks from last week


For example : if we are in week 37 than it should look for number 37 in Row 1 and calculate last 6 cells from last week


My weeks numbers are in row 1

Orders are in row 2


So formula that find this week number in row 1 and than go below one row and calculate last 6 cells of row 2


Attached worksheet makes more sense


Thanks guys in advance

 

[Chihiro]

Try this. Assuming Week start on Sunday.

=SUMIFS($B$2:$BB$2,$B$1:$BB$1,"<="&(WEEKNUM(TODAY(),1)-1))-SUMIFS($B$2:$BB$2,$B$1:$BB$1,"<="&(WEEKNUM(TODAY(),1)-7))

 

[PaulF]

Another option for you...

=SUM(INDEX($B$2:$BB$2,1,MATCH(WEEKNUM(TODAY(),1)-1,$B$1:$BB$1,0)):INDEX($B$2:$BB$2,1,MATCH(WEEKNUM(TODAY(),1)-6,$B$1:$BB$1,0)))

 

[ALAMZEB]

great guys

thanks a lot

 

[Khalid NGO]

Hi Alamzeb, and all,

Another, shorter one:

=SUM(IF($B$1:$BB$1<=(WEEKNUM(TODAY(),1)-1),IF($B$1:$BB$1>(WEEKNUM(TODAY(),1)-7),$B$2:$BB$2)))

To be array entered (CSE).

Regards,