How to update parent row with symbol colors that reflect child rows using a formula?
Hi
I would like to have the parent row have a formula that feed up from the child rows. so for example:
The Overall Status is green if all subcategories are green.
The Overall Status is yellow if 13 subcategories are yellow.
The Overall Status is red if 4+ subcategories are yellow or 1+ subcategories are red..
Answers

Hi Nataly,
Try this:
=IF(OR(COUNTIF(CHILDREN(), "Red") >= 1, COUNTIF(CHILDREN(), "Yellow") >= 4), "Red", IF(COUNTIF(CHILDREN(), "Yellow") >= 1, "Yellow", "Green"))
Logic formulas read from lefttoright. This means that it will only read the second statement if the first statement is not met, so the order is very important. In your case, we are starting with the RED statement, moving to the YELLOW, then if neither are met, asking the formula to return GREEN.
Here's what each statement means, broken down:
RED Statement
=IF(OR(COUNTIF(CHILDREN(), "Red") >= 1, COUNTIF(CHILDREN(), "Yellow") >= 4), "Red"
If the count of Red Child Status balls is greater than or equal to 1, then return Red.
OR
If the count of Yellow Child Status balls is greater than or equal to 4, return Red.
Yellow Statement
IF(COUNTIF(CHILDREN(), "Yellow") >= 1, "Yellow"
If the previous criteria isn't met (there are no Reds, and the count of Yellow is UNDER 4)
and If the count of Yellow Child Status balls is greater than or equal to 1, turn yellow.
(We don't need to specify to stop at 4 because this is covered in our first, RED statement.
Green Statement
If the previous criteria isn't met (meaning there are no Reds and no Yellows) return green.
Keep in mind that this will stay true even if you have blank, blue, or grey balls as well.
Help Articles
Here are some Help Center articles I used to build this:
Let me know if you have any questions about this!
Cheers,
Genevieve

@Genevieve P.  this is fantastic! I just used it in my own Smartsheets. One question. If I have blue for all my children completed line items and want it to turn the parent row blue only when all the children are blue, how would I add this to the formula above?

Hi @Emily T.
I'm glad I could help! We can add one more statement to the formula above that COUNTs the number of Children and if it's the same as the COUNT of Blue Children, then turn Blue:
=IF(OR(COUNTIF(CHILDREN(), "Red") >= 1, COUNTIF(CHILDREN(), "Yellow") >= 4), "Red", IF(COUNTIF(CHILDREN(), "Yellow") >= 1, "Yellow", IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Blue"), "Blue", "Green")))
Cheers,
Genevieve

I've been trying to get this figured out, and I'm almost there, can you help? I'm using Red (Not Started); Yellow (On Hold), Green (In progress) and Blue (Complete).
I want if all children are Red, then the Parent is Red
If the children status contains any Yellow, Green or Blue, then Parent should be Green
If all the children are Blue, the Parent should be Blue.

Hi @Dawn_Cox
No problem! In your instance you'll want to compare a COUNTIF statement with a general COUNT(CHILDREN()).
You actually only need to write two statements:
If all children are Red, then the Parent is Red
=IF(COUNTIF(CHILDREN(), "Red") = COUNT(CHILDREN()), "Red",
and
If all the children are Blue, the Parent should be Blue.
IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue",
Then if neither of these statements are true, we know that it means there's at least one child that has a different colour. We can simply say "Green" at the end of the formula for this instance.
Try:
=IF(COUNTIF(CHILDREN(), "Red") = COUNT(CHILDREN()), "Red", IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", "Green"))
Cheers,
Genevieve

Thank you SO much! That works perfectly.
I am a brand new PM, and my supervisor/mentor just gave me a few projects to work on so I can get some experience.

No problem at all, I'm glad that worked! 🙂 Feel free to post any questions you have as New Questions and I'm sure the Community will be happy to help you with your new projects.
Top tip  you can "Collapse All" from the three dots next to the Primary Column name, so your sheet is only showing the Parent rows:
Then you can dragfill that formula down into all the Parent row cells without affecting the child rows:
Cheers!
Genevieve

Hello.
This worked well however how do you get it to be a column formula (when change it to a column formula it converts everything and removes my drop down selections)
=IF(OR(COUNTIF(CHILDREN(), "Red") >= 1, COUNTIF(CHILDREN(), "Yellow") >= 4), "Red", IF(COUNTIF(CHILDREN(), "Yellow") >= 1, "Yellow", "Green"))
I am hoping to have this as a standard up and down my sheet as needed
Thank you!!!

Hi @Morris R
You can either have a column formula (which applies the same formula to every single cell) or you can manually enter values.
I would suggest collapsing the parent rows as I show above and dragging the formula into the correct cells. That way you can have a mixture of formulas in the Parent rows and manual submissions in the child rows.
Cheers,
Genevieve
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!