Weakest link formula assistance

Tina

Hello!  I need to ask for some help.  

I am trying to set up a Weakest link formula.  

We would like to use Red, Yellow, Green for Health for each task and have a Weakest link formula at the Activity/Milestone level. 

• Once a task is complete we will manually use a Symbol column with a Star to indicate completion. 
• If a task is complete, indicated with a Star, I no longer want it to be counted in the weakest link formula if it is complete.  

My weakest link formula is working, except that it is not pulling out completed tasks. Does anyone have a solution?

This is the formula that I am using is below:

=IF(Level@row = 2, IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", "Green")), IF(Complete@row = 1, "Green", IF(Complete@row = 1, "Green", IF(AND(Complete@row = 0, [Due Date]@row < TODAY()), "Red", IF(AND(Complete@row = 0, [Due Date]@row < TODAY(7)), "Yellow")))))

Thank you!

Tina

Paul Newcome

Change each of your COUNTIF statements to COUNTIFS and include the range of

 

CHILDREN(Complete@row)

 

and criteria of 

 

@cell = 0

.

...........................IF(COUNTIFS(CHILDREN(), "Red", CHILDREN(Complete@row), @cell = 0) > 0, .......................................................