I am trying to create a checkbox column ([Parent]) that is set if the row has descendants that are flagged as being due in the coming 14 days or are overdue (this is calculated by another checkbox column [Due/At Risk]) AND if the checkbox column to mark a task as External is NOT flagged [External].

I have been trying all sorts of variations of formulas but can't seem to get it right where a descendant must be checked in one column AND unchecked in another, for this column to flag the parent row if the children meet this criteria.

I had it working when only requesting that [Due/At Risk] is checked, but when I try to add additional criteria to ensure external task parent rows are not flagged I seems to come back as invalid.

=IF(AND(COUNT(CHILDREN([Task Name]@row)) > 0, COUNTIFS(DESCENDANTS([Due/At Risk]@row), 1) > 0), 1)

Any help appreciated please.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!