Stumped trying to add OR to an otherwise working equation

Hello, I have a working equation that I need to take one step further, but have had no luck with my attempts.

I have a Status column that in it's simplest definition needs to return the values "Complete", "In Progress", or " " depending on the values in two other columns; ACTUAL START and ACTUAL FINISH. The list of initial criteria:

The equation in every row of the Status column should be the same; copied down to each row with no changes
If ACTUAL START has a date and ACTUAL FINISH has a date; status column @row should be "Complete"
If ACTUAL START has a date and ACTUAL FINISH is empty; status column @row should be "In Progress"
If ACTUAL START and ACTUAL FINISH are both empty; status column @row should be " "

Because I also need to factor in the status of parent rows based on the status of the children, additional criteria includes:

If row "isHeader" and all child row status are "Complete"; parent status row should be "Complete"
If row "isHeader" and at least one child row status is "Complete" or "In Progress"; parent status row should be "In Progress"
If row "isHeader" and all child row status are " "; parent status row should be " "

Where I need help now is figuring out how to have the status column return "Complete" when ACTUAL START and ACTUAL FINISH either have dates or have "n/a". You can see here, the equation is working for all rows except where I have "n/a" in ACTUAL START/FINISH:

Here I'm using this equation:

=IF(isHeader@row = 1, IF(COUNTIF(CHILDREN(Status@row), "Complete") = COUNT(CHILDREN([Task Name]@row)), "Complete", IF(SUM(COUNTIF(CHILDREN(Status@row), "Complete"), COUNTIF(CHILDREN(Status@row), "In Progress")) > 0, "In Progress", " ")), IF(AND(ISDATE([ACTUAL START]@row), ISDATE([ACTUAL FINISH]@row)), "Complete", IF(ISDATE([ACTUAL START]@row), "In Progress", " ")))

I can get the n/a to work in the Status with this adjustment to the end of the equation:

=IF(isHeader@row = 1, IF(COUNTIF(CHILDREN(Status@row), "Complete") = COUNT(CHILDREN([Task Name]@row)), "Complete", IF(SUM(COUNTIF(CHILDREN(Status@row), "Complete"), COUNTIF(CHILDREN(Status@row), "In Progress")) > 0, "In Progress", " ")), IF(AND([ACTUAL START]@row = "n/a", [ACTUAL FINISH]@row = "n/a"), "Complete", " "))

I tried adding an OR into the equation, but need a second set of eyes with what I'm missing that's resulting in #INCORRECT ARGUMENT SET

=IF(isHeader@row = 1, IF(COUNTIF(CHILDREN(Status@row), "Complete") = COUNT(CHILDREN([Task Name]@row)), "Complete", IF(SUM(COUNTIF(CHILDREN(Status@row), "Complete"), COUNTIF(CHILDREN(Status@row), "In Progress")) > 0, "In Progress", " ")), IF(AND(OR(ISDATE([ACTUAL START]@row), ISDATE([ACTUAL FINISH]@row)), [ACTUAL START]@row = "n/a", [ACTUAL FINISH]@row = "n/a"), "Complete", IF(ISDATE([ACTUAL START]@row), "In Progress", " ")))

Find more posts tagged with

Formulas

Support

Accepted answers

ssears23

Ah, figured it out with the help of this post (https://community.smartsheet.com/discussion/66204/trying-to-combine-2-and-functions-with-an-or). Leaving my solve for anyone else who may benefit:

=IF(isHeader@row = 1, IF(COUNTIF(CHILDREN(Status@row), "Complete") = COUNT(CHILDREN([Task Name]@row)), "Complete", IF(SUM(COUNTIF(CHILDREN(Status@row), "Complete"), COUNTIF(CHILDREN(Status@row), "In Progress")) > 0, "In Progress", " ")), IF(OR(AND(ISDATE([ACTUAL START]@row), ISDATE([ACTUAL FINISH]@row)), AND([ACTUAL START]@row = "n/a", [ACTUAL FINISH]@row = "n/a")), "Complete", IF(ISDATE([ACTUAL START]@row), "In Progress", " ")))

All comments

Kelly Moore

Hey @ssears23

I tried the formula directly above and I did not receive an error - it seemed to yield all the expected results. Was this the formula you needed the additional eyes? Your syntax for the AND/OR is correct

=IF(isHeader@row = 1, IF(COUNTIF(CHILDREN(Status@row), "Complete") = COUNT(CHILDREN([Task Name]@row)), "Complete", IF(SUM(COUNTIF(CHILDREN(Status@row), "Complete"), COUNTIF(CHILDREN(Status@row), "In Progress")) > 0, "In Progress", " ")), IF(AND(OR(ISDATE([ACTUAL START]@row), ISDATE([ACTUAL FINISH]@row)), [ACTUAL START]@row = "n/a", [ACTUAL FINISH]@row = "n/a"), "Complete", IF(ISDATE([ACTUAL START]@row), "In Progress", " ")))

Kelly

ssears23

Thanks @Kelly Moore! You're right, this version of the equation wasn't returning the error, but it's not returning the expected results. I'm getting everything as "In Progress" and the n/a line is getting a " " status instead of "Complete":

ssears23

Ah, figured it out with the help of this post (https://community.smartsheet.com/discussion/66204/trying-to-combine-2-and-functions-with-an-or). Leaving my solve for anyone else who may benefit:

=IF(isHeader@row = 1, IF(COUNTIF(CHILDREN(Status@row), "Complete") = COUNT(CHILDREN([Task Name]@row)), "Complete", IF(SUM(COUNTIF(CHILDREN(Status@row), "Complete"), COUNTIF(CHILDREN(Status@row), "In Progress")) > 0, "In Progress", " ")), IF(OR(AND(ISDATE([ACTUAL START]@row), ISDATE([ACTUAL FINISH]@row)), AND([ACTUAL START]@row = "n/a", [ACTUAL FINISH]@row = "n/a")), "Complete", IF(ISDATE([ACTUAL START]@row), "In Progress", " ")))

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.