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>
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,071
Office Version
2010
Platform
Windows
You can use an equation like this:
=SUM(OFFSET(B6:B9,0,MATCH(B1,B5:BA5,0)))
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,490
@ 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:

alzoell

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

Forum statistics

Threads
1,089,201
Messages
5,406,801
Members
403,106
Latest member
AliO

This Week's Hot Topics

Top