How to update parent row with symbol colors that reflect child rows using a formula?

Nataly Hart

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 1-3 subcategories are yellow.

-The Overall Status is red if 4+ subcategories are yellow or 1+ subcategories are red..

Genevieve P.

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 left-to-right. 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:

• IF Statement
• COUNTIF Function
• CHILDREN Function
• OR Function
• Using = and >
• Formula FAQs

Let me know if you have any questions about this!

Cheers,

Genevieve

Emily T.

@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?

Genevieve P.

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

Dawn_Cox

@Genevieve P.

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.

Genevieve P.

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

Dawn_Cox

@Genevieve P.

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.

Genevieve P.

@Dawn_Cox

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 drag-fill that formula down into all the Parent row cells without affecting the child rows:

Cheers!

Genevieve

Morris R

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!!!

Genevieve P.

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