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:

1. Finds the row where "Task Name" appears in the Task Name column
2. References the corresponding cell in the Task Complete? column
3. 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!

Kelly Moore

Hey @Zachary Kelley

Is it possible for a screenshot of your sheet in question? Specifically I need to see where you are placing the formula and what other data might be present in the same column.

If the formula is on the same sheet and in a column with no other manually entered data then you can try this formula:

=IF(COUNT(CHILDREN([enter name of your primary column]@row))>0, COUNTIFS(CHILDREN([Task Complete?]@row), 1) / COUNT(CHILDREN([Task Complete?]@row)))

This formula first checks if the row is a Parent row, then calculates the %complete. This will calculate automatically assuming you aren't looking for a specific level of Parent. If that is the case then let me know as that can also be calculated automatically

Kelly

Zachary Kelley

Hey @Kelly Moore,

The formula would be placed in a field in the sheet summary. The primary column is "Task Name" and contains the name of milestones and the subtasks to reach the milestones - each milestone is a parent row and each subtask is a child of that parent row. Within the sheet summary, I'm doing a simple % completion calculation on the checkbox column (=COUNTIF(CHILDREN([Task Complete?]73), 1) / COUNT(CHILDREN([Task Complete?]73))) for every milestone (or parent row) - each milestone has its own field in the sheet summary that specifically references its row.

The problem I'm trying to solve is that when I make broad updates to the sheet summary and copy it to about 100 trackers, I have to manually go into each sheet summary and fix the row numbers in that formula. Given that the milestone names are static, I'm trying to find a formula that will perform the same calculation I'm currently doing, but instead of referencing arbitrary row numbers, it first matches the milestone name to perform the calculation. Could be a big time saver.

Since I'm keeping these calculations in the sheet summary, your proposed function isn't going to work.

Thanks!

Kelly Moore

Hey @Zachary Kelley

Currently hierarchical functions like CHILDREN, ANCESTORS etc are not supported across sheets so you will need a couple of helper columns on your source sheet.

I'm not sure how your Summary Sheet is set up regarding the referencing of Task Names in the Source Sheet. Sometimes it is necessary to concatenate names to group all the relevant rows together.

These are the helper columns on your SOURCE sheet

[Parent Helper] checkbox column

=IF(COUNT(CHILDREN([Task Name]@row))>0, 1)

The second helper places the Task Name on every row so it can be collected on the cross sheet. If you don't need it concatenated but only need the Parent name, there is a simpler formula to use

[Task-Name] Text/Number column

=IF([Parent Helper]@row = 1, JOIN(ANCESTORS([Task Name]@row), "-") + "-" + [Task Name]@row, PARENT())

Again, depending on the structure of your Source Sheet, you may want/need a 3rd helper column that I call 'Level'. This will identify the hierarchy level of your row. It is sometimes needed to call specific levels into a COLLECT function. It is my personal preference to have this helper on any sheet with GrandParents, Great Grand Parents, etc.

=COUNT(ANCESTORS())

The dynamic formula in your Target Sheet looks like this

=COUNTIFS({Source Sheet Task-Name}, [Task Name]@row, {Source Sheet Parent Helper}, 0, {Source Sheet Status}, 1) / COUNTIFS({Source Sheet Task-Name}, [Project Status]@row, {Test Sheet Parent Helper}, 0)

*This boldfaced criteria needs to match the text in the [Task-Name] column in the Source sheet. You can use CONTAINS or others to filter for the correct info.

Let me know if something needs tweaking.

Kelly

Humashankar

Good analysis

Zachary Kelley

Hey @Kelly Moore,

I'm calculating "% Complete" from a field in the sheet summary, not a separate summary sheet. In order to get a portfolio level view of project milestone percent completion, I will run a sheet summary report on every project in the portfolio.

Example:
NTP/COD are in the "Task Name" column, the checkboxes are in the "Task Complete?" column.

Again, the problem I'm trying to solve is that when I make broad updates to the sheet summary and copy it to about 100 trackers, I have to manually go into each sheet summary and fix the row numbers in that formula. Given that the milestone names are static, I'm trying to find a formula that will perform the same calculation I'm currently doing, but instead of referencing arbitrary row numbers, it first matches the milestone name to perform the calculation.

Sorry for the confusion.

Thanks!

Kelly Moore

@Zachary Kelley

To clarify, you are wanting each Parent row to show you the %Complete for that Parent? Unfortunately, a Summary Field will not be able to do that dynamically without some reference that tells the Summary Field which Parent, in all the Parent rows, you are trying to determine. You cannot use @row in a Summary Field as it is not sitting on any particular row. I'm not sure how many Parents you have but you would have to set up a Summary Field for each one and hardcode either the Name of the Milestone in the field or, as you have been doing, the row number.

If you add a %Complete column to the sheet you can calculate %Complete easily at the row level. This information could be pulled into a report- you could include many sheets into the report to assimilate the information. Or, if you did want the information compiled at the sheet level, you could also consider using Copy row automation to copy the Parent Rows over to a new sheet (you would need the Parent Helper column). Or finally, if you have access to Pivot App you could gather your %Complete's that way. All of three of these solution rely on the %Complete at the row level.

Would any of these work for you?
Kelly