Sumifs from Source Page

Hello,
I have a source sheet where data is input daily. It collects among other things the number of reservations and contracts for each day. As you can see it has a date, reporting column (where the distinctions are made), Reservations column, a contracts column, and a Week # helper column.
Below is my Summary Sheet. It has where I want to sum the total # of Contracts + Reservations if the Week column in the source sheet is 1 less than the cell below that has the Current Week (see 44 below).
How can I write that formula?
Best Answer
-
Try adding two separate cross sheet SUMIF formulas together with a + symbol, like this:
=SUMIF({Week Column}, [Cell Reference]$6 - 1, {Contracts Column}) + SUMIF({Week Column}, [Cell Reference]$6 - 1, {Reservations Column})
When you're writing this out, click on the cell that currently has 44 to populate the correct column name and row reference. Then lock the formula to that row with the $ sign, like I have in my example:
[Cell Reference]$6
Then you - 1 to have one week earlier.
Let me know if this makes sense or if I can clarify anything further!
Cheers,
Genevieve
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Answers
-
Try adding two separate cross sheet SUMIF formulas together with a + symbol, like this:
=SUMIF({Week Column}, [Cell Reference]$6 - 1, {Contracts Column}) + SUMIF({Week Column}, [Cell Reference]$6 - 1, {Reservations Column})
When you're writing this out, click on the cell that currently has 44 to populate the correct column name and row reference. Then lock the formula to that row with the $ sign, like I have in my example:
[Cell Reference]$6
Then you - 1 to have one week earlier.
Let me know if this makes sense or if I can clarify anything further!
Cheers,
Genevieve
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
Thank you@Genevieve P., worked beautifully as always!!
-
Wonderful! Thanks for letting me know. 🙂
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!