IF/AND and PARENT/DESCENDANTS

SJGaffney

Hello,

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.

SJGaffney

I got it working in the end:

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

Andrée Starå

Hi @SJGaffney

I hope you're well and safe!

Excellent! Glad you got it working!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!