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:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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?

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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!