Using IF, OR, and CONTAINS, ISBLANK to track status of actions
Hello, I'm attempting to autofill the primary row for each project with subtasks under each project. The status of subtasks vary from "Not Started", "In Progress", "Outstanding", "Complete".
I'm trying to create a formula that examines the set of rows' status under the project to summarize the overall status of the project. I'm trying to simplify it by being either "In Progress" or "Complete"
Is there a way to do this?
=IF(OR(CONTAINS("In Progress", [Status of Actions]71:[Status of Actions]76), CONTAINS(ISBLANK([Status of Actions]71:[Status of Actions]76, [Status of Actions]71:[Status of Actions]76))), "In Progress", "Complete")
Best Answer
-
This will allow the parent task to be "Not Started", "Complete", or "In Progress"
=IF(COUNT(CHILDREN(Task@row)) = COUNTIF(CHILDREN(Status@row), "Complete"), "Complete", IF(COUNT(CHILDREN(Task@row)) = COUNTIF(CHILDREN(Status@row), "Not Started"), "Not Started", "In Progress"))
Answers
-
What about this?
If the total number of subtasks (determined by counting the number of values in the "Primary Column", in this case Task) is equal to the number of rows where the status is "Complete", then the parent row is "Complete", else it is "In Progress".
=IF(COUNT(CHILDREN(Task@row)) = COUNTIF(CHILDREN(Status@row), "Complete"), "Complete", "In Progress")
-
@SJ Sellers Brilliant, this seems to work. Never thought about using the CHILDREN function. Thank you so much! For now this seems to be great, wondering if there would be a way of having another option for "Not Started"?
-
This will allow the parent task to be "Not Started", "Complete", or "In Progress"
=IF(COUNT(CHILDREN(Task@row)) = COUNTIF(CHILDREN(Status@row), "Complete"), "Complete", IF(COUNT(CHILDREN(Task@row)) = COUNTIF(CHILDREN(Status@row), "Not Started"), "Not Started", "In Progress"))
-
Hi @Mary_A Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!