Formula For Summing Workload by Assignee by Week

Tori Heath

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.

Ryan Sides

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.

Ryan Sides

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.

Tori Heath

Perfect! Thanks so much @Ryan Sides!

Ryan Sides

You bet! Enjoy.