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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thank you@Genevieve P., worked beautifully as always!!
-
Wonderful! Thanks for letting me know. 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!