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.

Find more posts tagged with

Manufacturing

Construction

Formulas

Support

Project Management

Accepted answers

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

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.