Help with Formula

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?


Answers

  • SteyJ
    SteyJ ✭✭✭✭✭✭

    try this:

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

    Jacob Stey

  • Hey @SteyJ


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


  • DKazatsky2
    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
    SteyJ ✭✭✭✭✭✭
    edited 12/20/23

    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))
    


    Sincerely,

    Jacob Stey

  • 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
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!