IF Statement - Compare many measures with hierarchy
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!
Comments
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Ah. Makes sense.
-
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
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
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")))
-
My bet is on the range as well.
Let us know how it goes!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
You guys are WONDERFUL! Thank you so much, that worked :-)
-
Thanks!
Happy to help!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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