How to sum vertical data with horizontal criteria?

[alzoell]

I'm stumped on how to automatically sum data from a rolling report. The report I'm using is pre-set and only comes back in 1 format, with the criteria I need to use across the top with my sales cascading below. I want this to automatically calculate last week's total sales whenever I put the new data in weekly. So this week I need it to sum everything under 201921, and next week I want to enter 201922 into my criteria box and have it sum everything under the column header of 201922 and so on throughout the rest of the year. And this report always includes all previous weeks in the year so it'll need to capture all 52 weekly columns.
I've tried =sumif($B$1:$BA$1,"201921",$B$2:$BA$150) and that formula with the criteria referencing a cell that I have to automatically populate with the last week (201921 in this case).
These formulas got the result of just the first cell, "3" instead of the sum I need, "22".
Thanks!


[TABLE="width: 500"]
<tbody>[TR]
[TD]Last Week:[/TD]
[TD]201921[/TD]
[/TR]
[TR]
[TD]Last Week Total Sales:[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Item Name:[/TD]
[TD]201920[/TD]
[TD]201921[/TD]
[/TR]
[TR]
[TD]Item A[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Item B[/TD]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Item C[/TD]
[TD]7[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Item D[/TD]
[TD]6[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[offthelip]

You can use an equation like this:
=SUM(OFFSET(B6:B9,0,MATCH(B1,B5:BA5,0)))

 

[Tetra201]

@ offthelip:

OFFSET is a volatile function. Here are two non-volatile options:

=SUM(INDEX($B$2:$BA$150,0,MATCH(201921,$B$1:$BA$1,0)))
=SUMPRODUCT(($B$1:$BA$1=201921)*$B$2:$BA$150)

 

[alzoell]

Thank you, this is much closer to what I need. I did a test run and realized that it is adding the current week instead of the previous week I specified as the criteria, and when I added some fake numbers for future weeks it rolled all of the future weeks plus the current week together. Is there a way to get it only add a single, previous week each time as the columns grow to the right throughout the year? My cell with the formula listing the criteria will be stationary in the sheet.

 

[alzoell]

@ Tretra201, the sum-index-match worked perfectly! Thank you so much!

 

[Tetra201]

Glad to have helped.