IF statement for check mark box and parent cell

edited 04/14/22 in Formulas and Functions

Can anyone help me with creating a formula for the following thought:

Check mark yes in this row if the parent cell is a specific name?


Edit: Also want to add that there are multiple parent cells under this parent cell so I want to include all of them in the check box formula.

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    If I'm reading this correctly, you want to check the box if the Task column in ANY parent/grandparent/great grandparent row above the given row in its hierarchy = "PSA", yes? Essentially this:

    If so, here's the formula for your checkbox column:

    =IF(COUNTIFS(ANCESTORS(Task@row), "PSA") > 0, 1, 0)

    In English: Count if there are ancestor cells of the task column from this row that are equal to PSA, and if that count is greater than 0, check the box.

    In the first row hierarchy grouping my screenshot above, because the top-most cell = "PSA", all the descendant rows in the hierarchy get checked. In the second row hierarchy grouping, the four checked rows all have a parent or grandparent row that is = "PSA", but other parent and child rows do not so they are not checked.


    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!