How can i calculate the % Complete at a sub-parent level based on multiple criteria?

Options

Hello and thanks in advance for any assistance. I have spent a ton of time attempting to solve and am tossing up the flag. I have a sheet that needs minimal column formulas due to the line/cell count potentially hitting the base reference limits.

I have an overall % complete being calculated on the sheet itself already. now i need to have individual ones for each of the sub-parents highlighted as "TSYS" & "Fiserv".

It needs to be based on "NA" being 0 and the "Status" being either "P" or "C". So essentially an average of the children based on that criteria. However these sub-parent titles are not unique as this is a baseline that gets copied out multiple times. looking to imbed the formula on those lines so when copied it goes along with it.

Answers

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Options

    I think what you are asking for is to give the percentage of tasks under a parent that are either P or C. This can be accomplished using the Children formula.

    =COUNTIF(CHILDREN(Status@row), OR(@cell = "P", @cell = "C")) / COUNT(CHILDREN(Status@row))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!