Formula For Summing Workload by Assignee by Week
Hi Smartsheet pros - I'm hoping to find a formula that can tally hours worked per person by week # across different projects. It'd be something like if the Week # falls within the Start and End Week of a project and its assigned to a certain person, sum Hours/Week. The results for Tori for Week 1, as an example, should be 15 since she's working on both Projects A and B during Week 1.
I am super stumped by this one and would love any suggestions you may have.
Best Answer
-
Hey @Tori Heath , give his one a try...
=SUMIFS([Hours/Week]1:[Hours/Week]4, [Start Week]1:[Start Week]4, <=Project@row, [End Week]1:[End Week]4, >=Project@row, Assignee1:Assignee4, HAS(@cell, "Tori Heath"))
It checks to see if the Week Number on the row is greater than or equal to the Start Week, is less than or equal to the End Week, and matches to the Persons full name.
Answers
-
Hey @Tori Heath , give his one a try...
=SUMIFS([Hours/Week]1:[Hours/Week]4, [Start Week]1:[Start Week]4, <=Project@row, [End Week]1:[End Week]4, >=Project@row, Assignee1:Assignee4, HAS(@cell, "Tori Heath"))
It checks to see if the Week Number on the row is greater than or equal to the Start Week, is less than or equal to the End Week, and matches to the Persons full name.
-
Perfect! Thanks so much @Ryan Sides!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!