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 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
Answers
-
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!
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!