Is there a formula to specify "Completed" if children "Completed" or "Nothing to Submit"?
The formula I'm using currently returns the cell as blank if children are "completed" or "nothing to submit", but I would like it to return "Completed" in that case.
=IF(COUNTIF(CHILDREN(), "Complete") + COUNTIF(CHILDREN(), "Nothing to Submit") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Not Started") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Blocked") > 0, "In Progress")))))
Best Answer
-
Oh! I see now.
Go back to CHILDREN but update "Complete" to "Completed".
Answers
-
Try using DESCENDANTS instead of CHILDREN.
-
Unfortunately, even with this change the status cells for the parents ("Provide Submissions" and "Proposed Rec Dept Submissions") remain blank.
This is the updated formula I used...
=IF(COUNTIF(DESCENDANTS(), "Complete") + COUNTIF(DESCENDANTS(), "Nothing to Submit") = COUNT(DESCENDANTS()), "Complete", IF(COUNTIF(DESCENDANTS(), "Not Started") = COUNT(DESCENDANTS()), "Not Started", IF(COUNTIF(DESCENDANTS(), "In Progress") > 0, "In Progress", IF(COUNTIF(DESCENDANTS(), "Not Started") > 0, "In Progress", IF(COUNTIF(DESCENDANTS(), "Blocked") > 0, "In Progress")))))
Any other suggestions?
Thanks,
Sara
-
Oh! I see now.
Go back to CHILDREN but update "Complete" to "Completed".
-
But of course! Ugh...the little details.
Thanks for being a second set of eyes!
Sara
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!