How to sum vertical data with horizontal criteria?

alzoell

New Member
Joined
May 21, 2019
Messages
3
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!


Last Week:201921
Last Week Total Sales:

<tbody>
</tbody>

Item Name:201920201921
Item A13
Item B42
Item C712
Item D65

<tbody>
</tbody>
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
You can use an equation like this:
=SUM(OFFSET(B6:B9,0,MATCH(B1,B5:BA5,0)))
 
Upvote 0
@ 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)
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top