# Add child rows that meet certain criteria and ALL their parents in a report

Options
✭✭
edited 11/29/23

Hello, I want to do the following:

I have a plan built with several levels of granparent, parent, child rows

I want to add in a report all rows that have Status: Complete and ALL their parent and grandparents for contextual information. The report should look like this

Grandparent

Parent

Child 1 Complete

Child 3 Complete

I'm using the following formula but its not working:

=IF(COUNTIFS(ANCESTORS([Task Name]@row), (Status@row = "Complete")) > 0, 1)

1) Can I do this type of filtering from the report sheet filter capabilities or do I need to create a helper column in the actual sheet I'm getting the data from?

2)Which formula would you recommend to checkbox both child rows that meet criteria and ALL their ancestor rows (even if they dont meet criteria)?

Thank you

• ✭✭✭✭✭✭
Options

Try this:

=IF(OR(COUNTIFS(DESCENDANTS(Status@row), @cell = "Complete")> 0, Status@row = "Complete"), 1)

• ✭✭
Options

Can you explain the meaning of this formula please: I need to flag basically each row where status is "Complete" and all the parents and grandparents of that row.

• ✭✭✭✭✭✭
Options

Try this:

=IF(OR(COUNTIFS(DESCENDANTS(Status@row), @cell = "Complete")> 0, Status@row = "Complete"), 1)

• ✭✭
edited 12/01/23
Options

Hello @Paul Newcome, thank you this worked perfectly. If I want to add one more condition (Status=Complete + End Date=Last 7 days) so child rows complying with this criteria (meaning, completed task in last 7 days) get flagged and ALL their parents and grand parents. What would you suggest?

Regards

• ✭✭✭✭✭✭
Options

You would add another range/criteria set to the COUNTIFS as well as an AND function within the OR to group the Status@row and [End Date]@row.

=IF(OR(COUNTIFS(DESCENDANTS(Status@row), @cell = "Complete", DESCENDANTS([End Date]@row), @cell>= TODAY(-7))> 0, AND(Status@row = "Complete", [End Date]@row>= TODAY(-7))), 1)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!