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
-
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
-
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.
-
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.
-
Sounds good! Glad I could help unstick you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!