# How to make Parent helper column reflect values based on Children values with multiple conditions

✭✭

I am very new at using formulas with hierarchy. I simply cannot figure this out or find a similar situation to manipulate the formula. I want the Parent value in helper column Parent Proficiency to be based on CHILDREN values in Proficiency column

Levels:

Untrained

In Process

Trained

Proficient

Preceptor

Parent helper row value should reflect lowest rating in the children rows except for the following:

If all CHILDREN = Untrained then PARENT = Untrained

If CHILDREN Untrained >= 1 and any others >0 = In Process

Parent row is "Place an intravenous catheter in a stable patient" with children Canine and Feline

Tags:

• ✭✭✭✭✭✭

Hey @decrom

Try this and see if it produces the expected result. Please be sure to test one 'In Process' with ZERO 'Untrained'. Was this what you expected?

=IF(COUNT(CHILDREN(Proficiency@row)) > 0, IF(COUNT(CHILDREN(Proficiency@row)) = COUNTIFS(CHILDREN(Proficiency@row), "Untrained"), "Untrained", IF(AND(COUNTIFS(CHILDREN(Proficiency@row), "Untrained") >= 1, COUNT(CHILDREN(Proficiency@row)) > COUNTIFS(CHILDREN(Proficiency@row), "Untrained")), "In Progress", IF(COUNTIFS(CHILDREN(Proficiency@row), "In Process") >= 1, "In Process", IF(COUNTIFS(CHILDREN(Proficiency@row), "Trained") >= 1, "Trained", IF(COUNTIFS(CHILDREN(Proficiency@row), "Proficient") >= 1, "Proficient", IF(COUNTIFS(CHILDREN(Proficiency@row), "Preceptor") >= 1, "Preceptor")))))))

Please let me know what we need to tweak

Kelly

• ✭✭✭✭✭✭

Hey @decrom

Try this and see if it produces the expected result. Please be sure to test one 'In Process' with ZERO 'Untrained'. Was this what you expected?

=IF(COUNT(CHILDREN(Proficiency@row)) > 0, IF(COUNT(CHILDREN(Proficiency@row)) = COUNTIFS(CHILDREN(Proficiency@row), "Untrained"), "Untrained", IF(AND(COUNTIFS(CHILDREN(Proficiency@row), "Untrained") >= 1, COUNT(CHILDREN(Proficiency@row)) > COUNTIFS(CHILDREN(Proficiency@row), "Untrained")), "In Progress", IF(COUNTIFS(CHILDREN(Proficiency@row), "In Process") >= 1, "In Process", IF(COUNTIFS(CHILDREN(Proficiency@row), "Trained") >= 1, "Trained", IF(COUNTIFS(CHILDREN(Proficiency@row), "Proficient") >= 1, "Proficient", IF(COUNTIFS(CHILDREN(Proficiency@row), "Preceptor") >= 1, "Preceptor")))))))

Please let me know what we need to tweak

Kelly

• ✭✭

Thank you! Works perfectly!

Is it possible to then copy the values from the children rows to show up in the same column as part of the formula?

• ✭✭✭✭✭✭

Hey @decrom

=IF(COUNT(CHILDREN(Proficiency@row)) > 0, IF(COUNT(CHILDREN(Proficiency@row)) = COUNTIFS(CHILDREN(Proficiency@row), "Untrained"), "Untrained", IF(AND(COUNTIFS(CHILDREN(Proficiency@row), "Untrained") >= 1, COUNT(CHILDREN(Proficiency@row)) > COUNTIFS(CHILDREN(Proficiency@row), "Untrained")), "In Progress", IF(COUNTIFS(CHILDREN(Proficiency@row), "In Process") >= 1, "In Process", IF(COUNTIFS(CHILDREN(Proficiency@row), "Trained") >= 1, "Trained", IF(COUNTIFS(CHILDREN(Proficiency@row), "Proficient") >= 1, "Proficient", IF(COUNTIFS(CHILDREN(Proficiency@row), "Preceptor") >= 1, "Preceptor")))))), Proficiency@row)

Will this work for you?

Kelly

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!