I need help with a SUMIFS formula to calculate total hours for an assignee based on dates


I'm working with a sheet that has a start date and due date, a column for assignee name, and a column for Hours/Week they're working on a project. I'm trying to get a formula to calculate the total hours an assignee is working throughout the sheet between two dates. I'm currently trying the formula below but it's brining back 0. Any thoughts on what I'm doing wrong?

=SUMIFS([Hours/Week]:[Hours/Week], [Assigned To]:[Assigned To], "Angie Smith", [Start Date]:[Start Date], >=DATE(2022, 10, 1), [Due Date]:[Due Date], <=DATE(2022, 10, 31))


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Ok. In that case the logic would be

    Due Date is greater than or equal to Oct 1 and Start Date is less than or equal to Oct 31.

    =SUMIFS([Hours/Week]:[Hours/Week], [Assigned To]:[Assigned To], @cell = "Angie Smith", [Start Date]:[Start Date], @cell<=DATE(2022, 10, 31), [Due Date]:[Due Date], @cell>=DATE(2022, 10, 1))

    This will grab anything that has any overlap in the month of October.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!