Dynamically referencing parent task row in COUNTIF/COUNT formula for child task completion ratio?

Zachary Kelley

Hello Smartsheet Community,

I'm trying to calculate the ratio of completed child tasks for a specific parent task in my sheet.

Currently, I have a formula that works when I manually specify the parent task's row number: =COUNTIF(CHILDREN([Task Complete?]70), 1) / COUNT(CHILDREN([Task Complete?]70))

However, I want to make this dynamic by identifying the parent task row based on a specific value in the Task Name column (in this case, "Task Name"). I've attempted to use the MATCH function to find the correct row: =MATCH("Task Name", [Task Name]:[Task Name], 0) and this correctly identifies the row number. I've tried various combinations of INDEX, @row, and MATCH functions to integrate this into my original formula, but I'm continually encountering #UNPARSEABLE errors. The "Task Complete?" column is a checkbox type. What's the correct way to combine these elements in Smartsheet to create a formula that:

Finds the row where "Task Name" appears in the Task Name column
References the corresponding cell in the Task Complete? column
Counts the ratio of completed child tasks for that parent task
This is where I'm at:

=COUNTIF(CHILDREN([Task Complete?]MATCH("Task Name", [Task Name]:[Task Name], 0)), 1) / COUNT(CHILDREN([Task Complete?]MATCH("Task Name", [Task Name]:[Task Name], 0)))

I'd appreciate any guidance on the proper syntax and approach. Thank you!

Melissa Yamada

Hello @Zachary Kelley

Try this:

=IF([Task Name]@row = "Task Name", COUNTIFS(CHILDREN([Task Complete?]@row), 1) / COUNT(CHILDREN([Task Complete?]@row)))

Zachary Kelley

Hey @Melissa Yamada,

This is giving me an #UNPARSEABLE error from the field in the sheet summary. I don't think the @row syntax works from fields in the sheet summary.

Thanks!

Melissa Yamada

@Zachary Kelley

Since you're using Sheet Summary, I'd suggest adding a helper column (text/number column) on your sheet with a formula that will return the value of the parent row.

Try this:

Helper Column:

=PARENT([Task Name]@row)

Formula in the Sheet Summary:

=COUNTIFS(Parent:Parent, "Task Name", [Task Complete?]:[Task Complete?], 1) / COUNTIFS(Parent:Parent, "Task Name")

Zachary Kelley

@Melissa Yamada

This works! Thanks for the help!

Melissa Yamada

Happy to help! 😊