Help with Formula

Jessica Suarez

Hi,

In Description column row 2, I am trying to calculate the percentage of completed items divided by the total number of child items. I have a helper column as well that indicates the count of child items for Projections, Planning, Action.

For example, in the screenshot below for projections I am trying to calculate the number of completed items (0) divided by the total child count (2)

Does anyone know of a formula that could help with this?

SteyJ

try this:

=COUNTIF(CHILDREN(Status:Status), "Completed") / [Child Count]@row

Jessica Suarez

Hey @SteyJ

I actually am using that one and I'm receiving the #Unparseable error

DKazatsky2

Hi @Jessica Suarez,

You are getting the #Unparseable error because you have an extra ":" (colon) in the formula, it should be the following.

=COUNTIF(CHILDREN(Status:Status), "Complete") / [Child Count]@row

Having said that, I am not convinced that formula is going to give the results you are looking for. The formula is going to count every task marked complete in it's calculation, regardless of hierarchy. So, in your example above, the description is going to say "3.5" because the formula equates "7 / 2."

Please let us know if things are working as expected.

Hope this helps,

Dave

SteyJ

https://community.smartsheet.com/discussion/comment/410553#Comment_410553

You're right. Good eye.

I believe the correct formula would be

=COUNTIF(CHILDREN(Status2), "Completed") / COUNT(CHILDREN(Status2))

Where 2 would be the parent row of the children you're trying to count.

You can also get away with @row in your case, I believe

=COUNTIF(CHILDREN(Status@row), "Completed") / COUNT(CHILDREN(Status@row))

Jessica Suarez

Hello @DKazatsky2

You are correct. it's not achieving the results that I am looking for.

I was more so looking for the formula to calculate the completed children (2) over the count children (2)

May I ask why it's calculating in every single child item and not just considering the child items of the parent row that I'm on?

DKazatsky2

@Jessica Suarez

It was calculating all the children because it was given the entire column as the range instead of just the children of the row - @SteyJ corrected that with his revised formula.

I believe this formula is closer to what you are looking for.

=COUNTIF(CHILDREN(Status@row), "Complete") / COUNT(CHILDREN(Task@row))

Note: This does not count the task "Formalize findings with Brandon" as a child of "Projections" so be careful to setup hierarchy to show what you intend.