Overall Health based on not completed tasks
I am looking to update this formula I currently use in the parent of the Health column.
It currently looks at child rows below and returns the color of the most "severe" status. (if it find any red, it returns red)
What I need:
However, if the phase is completed and the rest of the tasks are on track, I don't want to report completed phases health in the over all parent health.
Module 1 Health should represent the most severe health for all non-completed children.
Current Formula I need to modify to exclude checked rows:
=IF(COUNT(CHILDREN([Module - Phases]@row)) > 0, IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", "Gray"))))
Can you help me modify my formula? I am looking to exclude any children rows in the count where checkbox is checked under "Completed"
Best Answer
-
Don't use Completed@row, use CHILDREN(Completed@row) instead. You have to give COUNTIFS the same size range for each argument. By telling it to check all the children for Red, but then check only the single row for Completed, it's erroring out.
So:
=IF(COUNT(CHILDREN([Module - Phases]@row)) > 0,
IF(COUNTIFS(CHILDREN(), "Red", CHILDREN(Completed@row), 0) > 0, "Red",
IF(COUNTIFS(CHILDREN(), "Yellow", CHILDREN(Completed@row), 0) > 0, "Yellow",
IF(COUNTIFS(CHILDREN(), "Green", CHILDREN(Completed@row), 0) > 0, "Green", "Gray"))))
Answers
-
Change to COUNTIFS and add additional criteria for the Completed@row, 0 (0 is unchecked, 1 is checked). Hope this helps!
-
Thanks ! I must be missing something as I get incorrect argument set
=IF(COUNT(CHILDREN([Module - Phases]@row)) > 0, IF(COUNTIFS(CHILDREN(), "Red", Completed@row, 0) > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow", Completed@row, 0) > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green", Completed@row, 0) > 0, "Green", "Gray"))))
-
Don't use Completed@row, use CHILDREN(Completed@row) instead. You have to give COUNTIFS the same size range for each argument. By telling it to check all the children for Red, but then check only the single row for Completed, it's erroring out.
So:
=IF(COUNT(CHILDREN([Module - Phases]@row)) > 0,
IF(COUNTIFS(CHILDREN(), "Red", CHILDREN(Completed@row), 0) > 0, "Red",
IF(COUNTIFS(CHILDREN(), "Yellow", CHILDREN(Completed@row), 0) > 0, "Yellow",
IF(COUNTIFS(CHILDREN(), "Green", CHILDREN(Completed@row), 0) > 0, "Green", "Gray"))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!