Common status: Parent/Children formula
I need a formula in a parent row to return the most common value of its children:
If there are equal or more reds than yellows or greens, the parent status should be red.
If there are equal or more yellows than reds or greens, the parent status should be yellow.
If there are equal or more greens than reds or yellows, the parent status should be green.
Otherwise, the parent status should be blue.
Best Answer
-
Try this!
=IF(AND(COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Green")), "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green")), "Yellow", IF(AND(COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Red")), "Green", "Blue")))
I think it works...?
Kind regards
Debbie
Answers
-
Try this!
=IF(AND(COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Green")), "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green")), "Yellow", IF(AND(COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Red")), "Green", "Blue")))
I think it works...?
Kind regards
Debbie
-
Hey Debbie
Thank you so much. This worked!
Kind regards,
Jana
-
Excellent! Glad it worked :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!