COUNTIF Formula for Multiple Statues and Conditions

hmiller22

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!

jmyzk_cloudsmart_jp

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"))))

https://app.smartsheet.com/b/publish?EQBCT=e553032233dd43c98a753dc22553df83

hmiller22

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.

Paul Newcome

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"))))

jmyzk_cloudsmart_jp

@hmiller22

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.)

https://app.smartsheet.com/b/publish?EQBCT=e553032233dd43c98a753dc22553df83

• 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
• 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)

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.