IF Statement - Compare many measures with hierarchy

molly.schwarzmiller83806

Hello - Looking for ideas on a formula to use for the following: I have a column where I am using the red, yellow, green symbols. The Smartsheet is listed by project where multiple projects measures will roll up to one overall customer measure. Each project will have a red, yellow or green symbol for the measure. For the overall customer, I want to say "If any of these projects = "Red", then "red", If any = yellow, then "yellow", otherwise "green". I want that to be the hierarchy as well so if any are red then it would be red. If none are red, but one is yellow, then yellow. Hope that makes sense. Thanks for any help!

Andrée Starå

Hi Molly,

Try something along the lines of.

=IF(COUNT(CHILDREN([Task Name]@row)) > 0; IF(COUNTIF(CHILDREN(); "Red") > 0; "Red"; IF(COUNTIF(CHILDREN(); "Yellow") > 0; "Yellow"; "Green")))  

The same version but with the below changes for your and others convenience.  

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", "Green")))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

Andree:

 

What is your reasoning for the first IF?

 

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, 

Andrée Starå

Paul.

If you'd want to use separate formulas for Parents and Children. Not needed for this scenario but I suspect it will be needed when OP has a look.

Best,

Andrée



Paul Newcome

Ah. Makes sense. 

molly.schwarzmiller83806

Hello Andree,

Thanks so much for your help on this. I put in the below formula, but I'm not getting any RYG symbol in return. There is no error message for the formula, it's just showing blank. I have one red, one yellow and one green ball in the Overall Project Status range, so I would expect it to return red. Any ideas?

=IF(COUNT(CHILDREN([Overall Project Status]22:[Overall Project Status]24)) > 0, IF(COUNTIF(CHILDREN([Overall Project Status]22:[Overall Project Status]24), "Red") > 0, "Red", IF(COUNTIF(CHILDREN([Overall Project Status]22:[Overall Project Status]24), "Yellow") > 0, "Yellow", "Green")))

Thank you!

Molly

Andrée Starå

Happy to help!

Can you share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Best,

Andrée

molly.schwarzmiller83806

Unfortunately, there seem to be restrictions where I can't share outside of my company. I created a smaller test sheet and took a screen shot here to show what my column for this formula looks like. Basically, I have to create a separate function for each of the grey lines to look at the white rows underneath it to determine red, yellow, green.

 

Paul Newcome

Could it be the range inside of the CHILDREN functions?

 

CHILDREN([Overall Project Status]22:[Overall Project Status]24))

 

Try changing it to just CHILDREN()

 

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", "Green")))

Andrée Starå

My bet is on the range as well.

Let us know how it goes!

Best,

Andrée

molly.schwarzmiller83806

You guys are WONDERFUL! Thank you so much, that worked :-)

Andrée Starå

Thanks! 

Happy to help!

Best,

Andrée

Paul Newcome

Happy to help!