Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Updating a Parent Row Based on Children's Status

✭✭✭✭✭
edited 01/16/25 in Formulas and Functions

Hello!

We are trying to create a formula to accommodate the following logic: IF any [RFP Status] of children HAS In Progress or Canceled return the value "In Progress", IF ALL [RFP Status] of children HAS Complete or Canceled return the value "Complete", Otherwise, Not Started.

I generated the formula below using AI, however, it's returning a value of Complete when some tasks are still In Progress. Please help :)

=IF(OR(CONTAINS("In Progress", CHILDREN([RFP Status]@row)), CONTAINS("Canceled", CHILDREN([RFP Status]@row))), "In Progress", IF(AND(COUNTIF(CHILDREN([RFP Status]@row), "Complete") + COUNTIF(CHILDREN([RFP Status]@row), "Canceled") = COUNT(CHILDREN([RFP Status]@row)), COUNTIF(CHILDREN([RFP Status]@row), "Complete") > 0), "Complete", ""))

Tags:

Best Answer

  • Community Champion
    Answer ✓

    Hi @Tony Fronza,

    See if this works out better for you.

    =IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(COUNTIF(CHILDREN(), "Complete") + COUNTIF(CHILDREN(), "Canceled") = COUNT(CHILDREN()), "Complete", "Not Started"))

    Notes:

    • This can only be put into the parent row, cannot be a column formula
    • If any of the [RFP Status]@row cells are blank, this will not work as the COUNT formula only includes cells with values

    Hope this helps,

    Dave

Answers

  • Community Champion
    Answer ✓

    Hi @Tony Fronza,

    See if this works out better for you.

    =IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(COUNTIF(CHILDREN(), "Complete") + COUNTIF(CHILDREN(), "Canceled") = COUNT(CHILDREN()), "Complete", "Not Started"))

    Notes:

    • This can only be put into the parent row, cannot be a column formula
    • If any of the [RFP Status]@row cells are blank, this will not work as the COUNT formula only includes cells with values

    Hope this helps,

    Dave

  • ✭✭✭✭✭

    @DKazatsky2 Thank you! This works for everything except for Not Started. The only time Not Started should appear is if ALL child rows are Not Started; otherwise, it should be in Progress. Any thoughts?

  • ✭✭✭✭✭

    @DKazatsky2 never mind! There was an issue with the sheet's formatting. It's working now! Thanks again.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions