Using IFS statement with date and check box

The simple statement is I want to have a sum of work hours in a department, that is still incomplete, during a calendar period.

Currently the formula is:

=SUMIFS([Work Hours]:[Work Hours], Department:Department, "Wire", Finish:Finish, >=DATE(2020, 9, 1), Finish:Finish, <=DATE(2020, 9, 30)) * 8

A suggested version has been =IF(Complete@row = 0, SUMIFS([Work Hours]:[Work Hours], Department:Department, "Wire", Finish:Finish, >=DATE(2020, 8, 1), Finish:Finish, <=DATE(2020, 8, 31)) * 8)

However, the two formulas return the same value.

Best Answer


  • You could use a SUM(COLLECT()) to get these results.

    SUM(COLLECT([Work Hours]:[Work Hours], Department:Department, "Wire", [End Date]:[End Date], >=DATE(2020, 9, 1), Complete:Complete, true))

    This is with a checkbox column showing when complete. You could also check ISBLANK() on the End Date if that is your determining factor on completion. I use this on a Sheet Summary for a number of departments. You also refer to another Sheet Summary to use a drop down to make the hours reported interactive. Hope this helps!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!