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

isma ✭✭
edited 11/29/23 in Formulas and Functions

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

Task Name Status



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

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!