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

Denera McCullough

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

Kelly Moore

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

Kelly Moore

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

Denera McCullough

This worked perfectly.