Using IFS statement with date and check box

Larry Bejcar

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.

Paul Newcome

You could also try this...

=SUMIFS([Work Hours]:[Work Hours], Department:Department, "Wire", Finish:Finish, AND(IFERROR(MONTH(@cell), 0) = 9, IFERROR(YEAR(@cell), 0) = 2020), Complete:Complete, 0) * 8

Kyle Moore

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!

Paul Newcome

You could also try this...

=SUMIFS([Work Hours]:[Work Hours], Department:Department, "Wire", Finish:Finish, AND(IFERROR(MONTH(@cell), 0) = 9, IFERROR(YEAR(@cell), 0) = 2020), Complete:Complete, 0) * 8