% Complete Children tasks on several hierarchy levels minus tasks that are N/A

Drop down options are N/A, Not Started, In Progress - On Track, In Progress - At Risks, Complete.

I want if children tasks are marked complete I want a % to calculate for those tasks marked complete minus N/A tasks.

Parent = 100% (correct)

Child 1 - Complete

Child 2 - Complete

Child 3 - Complete

Child 4 - Complete

Child 5 - Complete


Parent = 100% (correct)

Child 1 - N/A

Child 2 - Complete

Child 3 - Complete

Child 4 - Complete

Child 5 - Complete


Parent = 80% (This is incorrect and should be returned as 100%)

Child 1 - N/A

Child 2 - Complete

Child 3 - Complete

Child 4 - Complete

Child 5 - Complete

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Denera McCullough

    Try something like this

    IF(COUNT(CHILDREN([your primary column name]@row))>0, COUNTIFS(CHILDREN([your dropdown column name]@row), "Complete") / COUNTIFS(CHILDREN([your dropdown column]@row), <>"NA"))

    The first IF checks to see if this is a parent row. If it is, it counts based on the criteria within the countifs.

    cheers

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!