Multiple COUNTIF statements to return a value

Phoning a friend.. I am repurposing a sheet to be used for tracking recurring tasks. There may be activities either weekly or month is not required, so I want to mark the activity (parent) as “N/A” and not “Complete”. I have been able to adjust formula for everything except when all child status is are “N/A”. I believe I need to add another COUNTIF to the first statement that does a count of all Children that are “N/A” then Parent is “N/A”. 

Test Status – drop down for owner to update

Test Status – Formulaic is a helper column that contains the logic. It is used in a couple places on the sheet. 

Parent rows (in blue) for 1 and 3 are correct. Parent row 2 should be "N/A"

=IF([Step Number]@row = 0, IF(COUNTIF(CHILDREN([Task Status]@row), "") + COUNTIF(CHILDREN([Task Status]@row), "Not Started") = COUNT(CHILDREN(Week@row)), "Not Started",

IF(COUNTIF(CHILDREN([Task Status]@row), "Complete") = COUNT(CHILDREN(Week@row)), "Complete", IF(COUNTIF(CHILDREN([Task Status]@row), "Error") > 0, "Error",

IF(COUNTIF(CHILDREN([Task Status]@row), "In Progress") > 0, "In Progress",

IF(COUNTIF(CHILDREN([Task Status]@row), "Not Started") > 0, "In Progress", "Complete"))))),

IF([Task Status]@row = "", "Not Started", [Task Status]@row))

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    You could add another IF to evaluate if the COUNT of child rows with N/A is equal to the count of child rows and in that situation return "N/A". That is the part in bold here (remember the extra closing parenthesis towards the end).

    =IF([Step Number]@row = 0, IF(COUNTIF(CHILDREN([Task Status]@row), "") + COUNTIF(CHILDREN([Task Status]@row), "Not Started") = COUNT(CHILDREN(Week@row)), "Not Started", IF(COUNTIF(CHILDREN([Task Status]@row), "Complete") = COUNT(CHILDREN(Week@row)), "Complete", IF(COUNTIF(CHILDREN([Task Status]@row), "N/A") = COUNT(CHILDREN(Week@row)), "N/A", IF(COUNTIF(CHILDREN([Task Status]@row), "Error") > 0, "Error", IF(COUNTIF(CHILDREN([Task Status]@row), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN([Task Status]@row), "Not Started") > 0, "In Progress", "Complete")))))), IF([Task Status]@row = "", "Not Started", [Task Status]@row))

    It is a strange formula though.

    If the Step Number is 0,

    and all the child rows are Not Started or Complete or N/A it will return the same.

    and if not but any child row is Error, it returns Error

    and if not but any child row is In Progress or Not Started, it returns In Progress

    and if not it returns Complete

    If the Step Number is not 0 and the task status is blank it returns Not Started

    and if not it returns the Task Status.

    Is that the intention? I hope so, and I hope this extra part is what you need.

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    You could add another IF to evaluate if the COUNT of child rows with N/A is equal to the count of child rows and in that situation return "N/A". That is the part in bold here (remember the extra closing parenthesis towards the end).

    =IF([Step Number]@row = 0, IF(COUNTIF(CHILDREN([Task Status]@row), "") + COUNTIF(CHILDREN([Task Status]@row), "Not Started") = COUNT(CHILDREN(Week@row)), "Not Started", IF(COUNTIF(CHILDREN([Task Status]@row), "Complete") = COUNT(CHILDREN(Week@row)), "Complete", IF(COUNTIF(CHILDREN([Task Status]@row), "N/A") = COUNT(CHILDREN(Week@row)), "N/A", IF(COUNTIF(CHILDREN([Task Status]@row), "Error") > 0, "Error", IF(COUNTIF(CHILDREN([Task Status]@row), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN([Task Status]@row), "Not Started") > 0, "In Progress", "Complete")))))), IF([Task Status]@row = "", "Not Started", [Task Status]@row))

    It is a strange formula though.

    If the Step Number is 0,

    and all the child rows are Not Started or Complete or N/A it will return the same.

    and if not but any child row is Error, it returns Error

    and if not but any child row is In Progress or Not Started, it returns In Progress

    and if not it returns Complete

    If the Step Number is not 0 and the task status is blank it returns Not Started

    and if not it returns the Task Status.

    Is that the intention? I hope so, and I hope this extra part is what you need.

  • BobS7
    BobS7 ✭✭

    Thanks for the help. Added the additional statement and is now working as expected.

    Yes, the intent is to update and track the progress as the actions are completed. Knowing what is Not Started or Complete (all children have same status) is correct. N/A was added to account for weekly tasks that may be removed for one reason or another and I do not want to be showing as Not Started/Complete for actions removed.

    The purpose of the Step Number is not 0 is to account for when rows are added to a parent but status was not entered. By default everything is Not Started, once they begin the activity the owner updates the status.

    Greatly appreciate the help.

  • KPH
    KPH ✭✭✭✭✭✭

    Sounds good! Glad I could help unstick you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!