Check off a parent row when one or the other checkboxes in a child row is checked

Options

I have a sheet with parent rows that roll up a set of child rows. The child rows have 2 columns that are not applicable or complete...once ALL the child rows have either Not Applicable OR Complete, I'd like to check of Complete in the parent row. I can get my formula to check off the parent if all of the child rows are completed in a single column, but how do I mix and match between columns? I'm including a screen shot of the sheet.


Thanks for any help anyone can provide

Best Answer

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓
    Options

    Hey @WFeagin

    This will work assuming only Complete or Not Applicable is checked for each row. It checks the Complete box once the number of checks in the children rows reaches the number of children rows and only counts it if one of the two options are checked.

    =IF(COUNT(CHILDREN(Primary@row)) = COUNTIFS(CHILDREN([Not Applicable]@row), =1, CHILDREN(Complete@row), =0) + COUNTIFS(CHILDREN([Not Applicable]@row), =0, CHILDREN(Complete@row), =1), 1, 0)

Answers

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓
    Options

    Hey @WFeagin

    This will work assuming only Complete or Not Applicable is checked for each row. It checks the Complete box once the number of checks in the children rows reaches the number of children rows and only counts it if one of the two options are checked.

    =IF(COUNT(CHILDREN(Primary@row)) = COUNTIFS(CHILDREN([Not Applicable]@row), =1, CHILDREN(Complete@row), =0) + COUNTIFS(CHILDREN([Not Applicable]@row), =0, CHILDREN(Complete@row), =1), 1, 0)

  • WFeagin
    WFeagin ✭✭
    Options

    Thanks so much @Devin Lee! For it to work for me, I had to alter it slightly to:

    =IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN([Not Applicable]@row), =1, CHILDREN(Complete@row), =0) + COUNTIFS(CHILDREN([Not Applicable]@row), =0, CHILDREN(Complete@row), =1), 1, 0)


    When I tried it with IF(COUNT(CHILDREN(Primary@row)) I got an unparseable error. The change also allowed me to paste the formula into each parent row without having to edit it based on row location. It works fantastically, butt ime for me to practice more!

    Again, thanks so much

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!