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", " ")))
Best Answer
-
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", " ")))
Answers
-
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
-
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":
-
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", " ")))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!