Counting and applying "color status" for parent/children rows

JFrye

I have overall Annual goals (purple lines) with sub projects that fall into a particular category. I then have subtasks that fall under the corresponding item (identified with an alphanumeric task number). I want to be able to summarize (on a dashboard) a couple of things:

the task health of the project level (task 25 with a green indication in "Master Project R/Y/G" ) as well as the count of only the main subtasks...so a total number of 3 on the Action Tracker 2019 screenshot...) I have a metric that shows all lines (Capture 2) but can't figure out the syntax to only count the tasks at the "parent" or singular task level - i.e. only the tasks with whole numbers (no letters assigned). 

Thank you all so very much!

Alejandra

Hello,

You could enter the formula below in your status column to return the "weakest link". For example, if at least one child row has a red ball, the parent row will also display a red ball. If there isn't a red ball in the children rows, but there's at least one yellow ball, then the parent row will return a yellow ball, and so on:

=IF(COUNT(CHILDREN(Objective@row)) > 0, IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", "Gray"))))

Since the CHILDREN function references the direct children of a parent row and tasks 45, 66, and 25 are direct children rows of "SOLUTIONS, IMPLEMENTATION, AND PROJECT MANAGEMENT", you could consider entering the formula below in the Task column above task 45:

="Total Task Count: " + COUNT(CHILDREN(Objective@row))

For more informations on the functions used, see here:

• IF: https://help.smartsheet.com/function/if
• COUNT: https://help.smartsheet.com/function/count
• CHILDREN: https://help.smartsheet.com/function/children
• Formula Error Messages: https://help.smartsheet.com/articles/2476176-formula-error-messages

JFrye

Alejandra, thank you for your help. My boss wanted to change the structure, so I have deleted the purple lines and added the annual objectives to a Column (Objective/Grandparent). I still have the main projects identified by a numeric Task number and the subtasks have a number and a letter (61 is the main project and 61A is a part of that project. This is in Capture 1). Ideally, I want to be able to be able identify the aggregated completion percentage of each main project (tasks with only numbers) within each of the 4 main annual objectives (Objective/Grandparent column). I have averaged the %Complete (based on the date opened and est close dates with network days and divided how complete the project should be today - Col Est. Close %). If the Est Close % is less than the Task close%, the ball turns yellow. Boss-man wants a way to identify how far we are along with the aggregated Annual Objectives. Sorry if this is convoluted...I appreciate your thoughts!