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

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    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.

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!