# How to sum vertical data with horizontal criteria?

#### alzoell

##### New Member
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: 201920 201921 Item A 1 3 Item B 4 2 Item C 7 12 Item D 6 5

<tbody>
</tbody>

### 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
You can use an equation like this:
=SUM(OFFSET(B6:B9,0,MATCH(B1,B5:BA5,0)))

#### Tetra201

##### MrExcel MVP
@ 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
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

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