# % Complete Based on Child Conditions Met

Options
✭✭✭✭

Smartsheet Community:

I Need to Add to The Formula Below to Calculate % Complete Only if it is a Pass, Fail, or Not Applicable.

=ROUND(IF(COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()) > 0, COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()), 0) * 100, 0) + "%"

Below is a Screen Shot of what I am Trying to Accomplish.

Thanks

«1

• ✭✭✭✭✭✭
Options

You would use an IF statement like so:

=IF(OR(............................), run_%_formula)

=IF(OR([Test Status]@row = "Pass", [Test Status]@row = "Fail", [Test Status]@row = "Not Applicable"), ROUND(IF(COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()) > 0, COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()), 0) * 100, 0) + "%")

• ✭✭✭✭
Options

I am Receiving Circular Reference

• ✭✭✭✭
Options

I Removed the 1 and Change the Count if to Count because the Original Formula was Looking for a Checkbox. However, I am Still Receiving Circular Reference.

Modified Formula to Look For & Calculate For Only For Pass, Fail, and Not Applicable:

=IF(OR([Test Status]@row = "Pass", [Test Status]@row = "Fail", [Test Status]@row = "Not Applicable"), ROUND(COUNTIF(DESCENDANTS() / COUNT(DESCENDANTS()), COUNT(DESCENDANTS()) / COUNT(DESCENDANTS())) * 100, 0) + "%")

• ✭✭✭✭✭✭
Options

Where exactly are you putting the formula, and do you mean that the COUNTIF portions should only take into account those different Statuses?

• ✭✭✭✭
Options

The Formula will Be in the Test Status Column Row 1 (Parent Cell). It Should Count All The Status and Calculate The Total %. Therefore if Any of Those Statuses (Pass, Fail, Not Applicable) Appear, The Formula Should Count it and Display The %

The Question I am Answering is What % of Test have Been Executed and the Those 3 Status is The Criteria for Completion.

• ✭✭✭✭
Options

@Paul Newcome I have Been trying to Adjust this Formula to Look at the Child Statuses to Calculate the % Complete, but Unsuccessful. Any idea what I am doing wrong?

• ✭✭✭✭
edited 09/20/22
Options

@Paul Newcome I Think I am Close. I am Counting All Children or Descendants that are Not Blank and it Should Calculate % Complete for Pass, Fail, & Not Applicable Statuses Only. Can you Take a look at my Formula and See why I am Still Getting Circular Reference Below?

=IF(OR([Test Status]@row = "Pass", [Test Status]@row = "Fail", [Test Status]@row = "Not Applicable"), ROUND(IF(COUNTIF(DESCENDANTS(), @cell = "Pass", @cell = "Fail", @cell = "Not Applicable") / COUNT(DESCENDANTS()) <> "", COUNTIF(DESCENDANTS(), <>"") / COUNT(DESCENDANTS()), 0) * 100, 0) + "%")

As Reference My Original Formula is Calculating Based on the Child or Descendant Checkbox:

=ROUND(IF(COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()) > 0, COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()), 0) * 100, 0) + "%"

• ✭✭✭✭✭✭
Options

If you are putting the formula in the [Test Status] column then you cannot reference [Test Status]@row.

• ✭✭✭✭
Options

You Are Right! I Should Change it to Children and @row to @ cell, correct?

• ✭✭✭✭✭✭
Options

You don't need the @cell reference with hierarchy based functions. You should be able to just use CHILDREN and DESCENDANTS.

• ✭✭✭✭
Options

OK, like this?

ROUND(IF(COUNTIF(DESCENDANTS(), "Pass", "Fail", "Not Applicable")) / COUNT(DESCENDANTS()) <> "", COUNTIF(DESCENDANTS(), "Pass", "Fail", "Not Applicable") / COUNT(DESCENDANTS()), <>"") * 100, 0) + "%")

• ✭✭✭✭✭✭
Options

Yes, but the criteria that you are wanting to count for needs to be nested in an OR statement.

OR(@cell = "Pass", @cell = "Fail", @cell = "Not Applicable")

• ✭✭✭✭
Options

=ROUND(IF(COUNTIF(DESCENDANTS(), OR(@cell = "Pass", @cell = "Fail", @cell = "Not Applicable") / COUNT(DESCENDANTS()) <> "", COUNTIF(DESCENDANTS(), OR(@cell = "Pass", @cell = "Fail", @cell = "Not Applicable") / COUNT(DESCENDANTS()), <>"") * 100, 0) + "%"))

• ✭✭✭✭✭✭
Options

You will need to adjust your closing parenthesis. You are closing out the OR function but not the COUNTIF.

• ✭✭✭✭
Options

I did and Still Received an Error

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!