Help with getting my SUMIF formula to reference multiple tabs

[fdexcel1]

Hi,

I'm new here and I've never had official training on Excel for work. So far I've managed to figured everything out through Googling but this time I'm just stumped and can't figure it out so I'm hoping someone can give me a hand. Attached is the file.

FORECASThelp.xlsm

I do revenue at a hotel and the file is a forecast file for both room pick ups and for schedule purposes so the other departments know how many people to schedule based on the expected room pick up.

There are tabs "Jan" to "Dec" for me to forecast the pickups for each individual dates and then there's a "SCHEDULE" tab to figure out the number of arrivals and everything there is based on my pick up number. I enter.

What I'm trying to do is have whatever forecast number I input into any of the Jan to Dec tabs populate in one of the schedule cells as well. The issue is I can manage to get it work with 1 tabs using this formula

=SUMIF(Jan!B$1:AF$1,SCHEDULE!B$3,Jan!B$26:AF$26)

But I'm trying to get it to also work for all months, like if I'm forecasting from Jan 28th to Feb 2nd.

I tried something like
=SUMIF(Jan:Dec!B$1:AF$1,SCHEDULE!B$3,Jan:Dec!B$26:AF$26) but I get a value error.

For this specific file attached as an example, I'm trying to get the numbers from cell B22 colored in blue to fill in when I enter numbers for cells AE26 AF26 in Jan tab and B26 C26 in Feb tab.

EDIT: Sorry, forgot to mention the dates in cells B3 and onwards on the "Schedule" tab will changed if I change the dates in A2 in the "Schedule" tab. So I'm trying to make it so that If I'm scheduling say from Jan 28th and onwards, if I change the date to 1/28/2023, cells B22 to say G22 (on Schedule tab) should reflect that of cells AC26 to AC31 in Jan tab and cells B26 and onward in Feb tab and be able to do this for any of the monthly tab if I change the dates to reflect that month.

[Glenn Kennedy]

There is so much STUFF in there, it's not easy to see what you want.

What do you expect to see?

Where do you expect to see it?

[fdexcel1]

There is so much STUFF in there, it's not easy to see what you want.

What do you expect to see?

Where do you expect to see it?

Sorry, I know it's a bit confusing and I'm not sure how to best word it.

So pretty much this is where I enter the pick up numbers to be forecasted.

1.JPG

I want the "Pick up" cells in the "Schedule" tab, ex B22 to match that of the same date in the monthly tabs, in this case, "Jan" cell AE26

2.JPG

The current formula already works how I need it to except it's only formulated to work for Jan, I'm trying to get to work for all the monthly tabs.

For example, in Feb, notice cells B26 and C26 has input "10" in there for the dates of Feb 1st and Feb 2nd

3.JPG

But in Schedule, D22 and D23 shows 0 because I don't know how to make the formula work for all the monthly tabs. I want it to match whatever I input for the same dates in the Feb tab for the corresponding dates in the column so in this case, it's showing 0 but I want it to show 10 since that's what I have in Feb. And of course, if I put something in March or April, I'd want it to show up to.

4.JPG

The dates changed when you change the dates in cell A3 in the Schedule tab, I believe the uploaded file has a different date but just have to change it to 1/30/2023 to match the pictures.

[JeteMc]

Hello fdexcel1 and Welcome to Excel Forum.
Try pasting the following into cell B22 and then drag the fill handle over to cell V22:

Please Login or Register  to view this content.

Let us know if you have any questions.

[fdexcel1]

Hello fdexcel1 and Welcome to Excel Forum.
Try pasting the following into cell B22 and then drag the fill handle over to cell V22:

Please Login or Register  to view this content.

Let us know if you have any questions.

This worked perfectly! Thanks!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.