# Combine Child | Parent formulas into Column formula

Options
✭✭✭✭✭✭

I'm trying to redo our the Status formula for our project plan into a Column formula.

Parent row has this formula:

=IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", IF(CONTAINS("Green", CHILDREN()), "Green", IF(COUNTIFS(CHILDREN(), "Blue") = COUNT(CHILDREN([Phase Status]@row)), "Blue", IF(COUNTIFS(CHILDREN(), ISBLANK(@cell)) = COUNT(CHILDREN([Phase Status]@row)), "")))))

Child row has this formula:

=IF([Phase Status]@row = "Complete", "Blue", IF(AND([Planned Start Date]@row <= TODAY(), [Phase Status]@row <> "In Progress"), "Red", IF(AND([Planned End Date]@row <= TODAY(), [Phase Status]@row <> "Complete"), "Red", IF([Phase Status]@row = "On Hold", "Yellow", IF([Phase Status]@row = "In Progress", "Green")))))

Below is a screenshot of my setup:

When I combine these two, every Status ball (using symbol column for Status) turns to blue. I know I'm missing something but I can't figure out what. Any help would be appreciated.

• ✭✭✭✭✭✭
Options

Hi @Peggy Parchert

Hope you are fine, please try the following formula:

```=if(Level@row=0,IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow",
CHILDREN()), "Yellow", IF(CONTAINS("Green", CHILDREN()), "Green", IF(COUNTIFS(CHILDREN(), "Blue")
= COUNT(CHILDREN([Phase Status]@row)), "Blue", IF(COUNTIFS(CHILDREN(), ISBLANK(@cell))
= COUNT(CHILDREN([Phase Status]@row)), ""))))),IF([Phase Status]@row = "Complete", "Blue",
IF(AND([Planned Start Date]@row <= TODAY(), [Phase Status]@row <> "In Progress"),
"Red", IF(AND([Planned End Date]@row <= TODAY(), [Phase Status]@row <> "Complete"), "Red",
IF([Phase Status]@row = "On Hold", "Yellow", IF([Phase Status]@row = "In Progress", "Green"))))))
```

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

Hi @Peggy Parchert

Hope you are fine, please try the following formula:

```=if(Level@row=0,IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow",
CHILDREN()), "Yellow", IF(CONTAINS("Green", CHILDREN()), "Green", IF(COUNTIFS(CHILDREN(), "Blue")
= COUNT(CHILDREN([Phase Status]@row)), "Blue", IF(COUNTIFS(CHILDREN(), ISBLANK(@cell))
= COUNT(CHILDREN([Phase Status]@row)), ""))))),IF([Phase Status]@row = "Complete", "Blue",
IF(AND([Planned Start Date]@row <= TODAY(), [Phase Status]@row <> "In Progress"),
"Red", IF(AND([Planned End Date]@row <= TODAY(), [Phase Status]@row <> "Complete"), "Red",
IF([Phase Status]@row = "On Hold", "Yellow", IF([Phase Status]@row = "In Progress", "Green"))))))
```

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

@Bassam Khalil - Thank you! That worked. Appreciate the help.

Peggy

• ✭✭✭✭✭✭
edited 08/16/21
Options

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

@Bassam Khalil - could I trouble you again for some assistance?

I'm attempting to do another column formula column for my At Risk column of our project plan

Parent row formula:

=IF(COUNTIF(CHILDREN(), 1) > 0, 1)

Child row formula:

=IF(AND([End Date]@row < TODAY(), NOT(Status@row = "Blue")), 1)

Where my Status column is a symbol column (RYGB) and End Date is a Date column. I get an #UNPARSEBLE error message when I combine my two formulas.

Combined formula

=IF(COUNT(CHILDREN())>0, IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), 1), 1), IF(AND([End Date@row < TODAY(), NOT(Status@row = "Blue")), 1, 0))

What am I missing?

Thanks -Peggy

• ✭✭✭✭✭✭
Options

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

@Bassam Khalil - thank you. Invite sent.

• ✭✭✭✭✭✭
Options

Hope you are i fine, i fixed the formula for at risk column for parent and child please check it.

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

For the first I would actually suggest not leveraging the Level column since not all parent rows are Level 0. the most consistent way I have found to apply a formula to parent rows vs child rows when parents can be on different levels is to use an IF/COUNT/CHILDREN similar to the formula in your second question.

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

To avoid an error from having parenthesis or commas misplaced when combining the formulas, you can copy/paste while excluding the initial "=" sign.

So if you have this:

Parent row formula:

=IF(COUNTIF(CHILDREN(), 1) > 0, 1)

Child row formula:

=IF(AND([End Date]@row < TODAY(), NOT(Status@row = "Blue")), 1)

Then we would end up with something like this...

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, IF(AND([End Date]@row < TODAY(), NOT(Status@row = "Blue")), 1), IF(COUNTIF(CHILDREN(), 1) > 0, 1))

.

.

That first formula would turn out more like this instead which will ensure that the parent row formula really is applied to ALL parent rows instead of just the very first row at the top.

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, IF([Phase Status]@row = "Complete", "Blue", IF(AND([Planned Start Date]@row <= TODAY(), [Phase Status]@row <> "In Progress"), "Red", IF(AND([Planned End Date]@row <= TODAY(), [Phase Status]@row <> "Complete"), "Red", IF([Phase Status]@row = "On Hold", "Yellow", IF([Phase Status]@row = "In Progress", "Green"))))), IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", IF(CONTAINS("Green", CHILDREN()), "Green", IF(COUNTIFS(CHILDREN(), "Blue") = COUNT(CHILDREN([Phase Status]@row)), "Blue", IF(COUNTIFS(CHILDREN(), ISBLANK(@cell)) = COUNT(CHILDREN([Phase Status]@row)), ""))))))

• ✭✭✭✭✭✭
Options

Thank you @Paul Newcome

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!