COUNTIF Formula for Multiple Statues and Conditions
Hi, I'm struggling to have a working formula that would meet the conditions I am trying to have shown.
The 6 statuses are: Not Started, In Progress, Completed, Cancelled, On Hold, Delayed
So here's the logic I'm trying to apply:
- If all children are Not Started, the parent is Not Started
- If all children are In Progress, the parent is In Progress
- If all children are Completed, the parent is Completed
- If all children are Cancelled, the parent is Cancelled
- If all children are Completed OR Cancelled, the parent is Completed
- If all children are Completed OR Delayed, the parent is Delayed
- If all children are Completed OR On Hold, the parent is Delayed
- If one child is In Progress, the parent is In Progress
Appreciate the help and support!
Answers
-
Hi @hmiller22
In the following formula, use the first child if children have only one status, meaning all the children are "On Hold," for example. Then, if the children have "In Progress" status, the parent status is "In Progress."
Other than above, make sure the children have a "Completed" status; then, if the children only have "Completed," "Delayed," "On Hold," or "Cancelled" status, the parent status is "Delayed" of "Completed."; If the children have "Cancelled" status, the parent status is "Completed," otherwise, "Delayed."
=IF(COUNT(DISTINCT(CHILDREN(Status@row))) = 1, INDEX(CHILDREN(Status@row), 1), IF(HAS(CHILDREN(Status@row), "In Progress"), "In Progress", IF(HAS(CHILDREN(Status@row), "Completed"), IF(COUNTIF(DISTINCT(CHILDREN(Status@row)), OR(@cell = "Completed", @cell = "Delayed", @cell = "On Hold", @cell = "Cancelled")) = 2, IF(HAS(CHILDREN(Status@row), "Cancelled"), "Completed", "Delayed"), "Others"))))
or
- =IF(COUNT(DISTINCT(CHILDREN(Status@row))) = 1,
- INDEX(CHILDREN(Status@row), 1),
- IF(HAS(CHILDREN(Status@row), "In Progress"), "In Progress",
- IF(HAS(CHILDREN(Status@row), "Completed"),
- IF(COUNTIF(DISTINCT(CHILDREN(Status@row)), OR(@cell = "Completed", @cell = "Delayed", @cell = "On Hold", @cell = "Cancelled")) = 2,
- IF(HAS(CHILDREN(Status@row), "Cancelled"), "Completed", "Delayed"),
- "Others"))))
- =IF(COUNT(DISTINCT(CHILDREN(Status@row))) = 1,
-
I really appreciate your help! I'm still having issues with the formula you sent in my sheet. If all children are of the same status, the parent status will reflect the children's statuses. If I select "On Hold" or "Delayed" for any of the children, the parent reflects a blank value (unless 1 child has "In Progress" then the parent will show "In Progress").
The "In Progress" Logic works great.
-
Give this a try:
=IF(COUNTIFS(CHILDREN(), @cell = "In Progress")> 0, "In Progress", IF(COUNTIFS(CHILDREN(), @cell = "Not Started") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Not Started", IF(COUNTIFS(CHILDREN(), @cell = "Cancelled") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Cancelled", IF(COUNTIFS(CHILDREN(), OR(@cell = "Completed", @cell = "Cancelled")) = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Completed", "Delayed"))))
-
We must decide on undefined combinations, such as "Completed & Delayed & Not Started." (Delayed?, In Progress?) and add some rules to make it manageable. (We have 63 possible combinations! The sum from k equals 1 to 6 of C(6, k) =63, meaning there are 63 combinations to choose from 1 to 6 from 6 status.)
- Add New Completed Rule
- From your logic, "If one child is In Progress, the parent is In Progress," since Completed is better than In Progress, we modify your logic as follows;
- "If one child is In Progress or Completed, the parent is In Progress."
- exception: Completed & Canceled -> Completed
- From your logic, "If one child is In Progress, the parent is In Progress," since Completed is better than In Progress, we modify your logic as follows;
- Add Ignore Status Rule (Cancelled, Not Started)
- From your logic, "If all children are Completed OR Cancelled, the parent is Completed," we can ignore any "Cancelled" child item.
- So, for example, Cancelled & Delayed -> All Delayed.
- In a similar logic, let's add Not Started as one we can ignore when deciding the parent.
- (Check the Note on Neutral Child Status* at the bottom)
- From your logic, "If all children are Completed OR Cancelled, the parent is Completed," we can ignore any "Cancelled" child item.
RULES
Then, we can summarize the logic as below;
- Rule 1: IF All is one status -> the status
- Rule 2: IF COUNT(OR(In Progress, Completed) >=1 -> In Progress
- exception (Completed & Canceled -> Completed)
- Rule 3: IF HAS(Canceled, Not Started) -> Ignoring these.
- Rule 4: IF has more than one status, choose the less desirable status as the parent's status. (->prepare for the worst)
New Formula
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNT(DISTINCT(CHILDREN(Status@row))) = 1, INDEX(CHILDREN(Status@row), 1), IF(AND(COUNT(DISTINCT(CHILDREN(Status@row))) = 2, HAS(CHILDREN(Status@row), "Completed"), HAS(CHILDREN(Status@row), "Cancelled")), "Completed", IF(OR(HAS(CHILDREN(Status@row), "In Progress"), HAS(CHILDREN(Status@row), "Completed")), "In Progress", IF(COUNTIF(DISTINCT(CHILDREN(Status@row)), NOT(OR(@cell = "Cancelled", @cell = "Not Started"))) >= 1, IF(HAS(CHILDREN(Status@row), "On Hold"), "On Hold", IF(HAS(CHILDREN(Status@row), "Delayed"), "Delayed")))))))
**Note on Neutral Child Status:
- Canceled
- A single task within a larger project is canceled but does not affect the overall project's completion or objectives; the cancellation of that specific task can be considered neutral in the context of the project as a whole.
- Not Started
- Assuming, if "not started," but not yet, the expected start date has arrived. If the scheduled start date arrives and is not started, the status is "Delayed.' Therefore, such "Not Stated" child status is neutral to the parent's status.
- Add New Completed Rule
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!