Formula using Sum and Countif not returning expected outcomes
Here's a version of the formula that works. However I need to include a second value and get an error when I try. I'm sure it's the formula syntax. Not sure if there is a better way to get the right outcome. I basically am trying to determine % Complete OR Not Applicable
Working with just Pass as a value
=SUM((COUNTIF(CHILDREN(Status22), ="Pass")) / SUM(COUNT(CHILDREN(Status22))))
When a Not Applicable is selected the above formula results in a reduction to the % Complete and the desired outcome would be for Pass and Not Applicable to both count so if a pass or not applicable are used the % complete would be the same.
=SUM((COUNTIF(CHILDREN(Status22), ="Pass") + (COUNTIF(CHILDREN(Status22), ="Not Applicable")) / SUM(COUNT(CHILDREN(Status22))))
When I use the formula above I get a number much higher than 100% which is the max I should be getting
Not sure the right way to structure the formula to get a % complete based on the sum of both Pass and Not Applicable versus overall number of children. Which is how I think the formula should be structured. Any suggestions?
Comments

Without taking a hard look at it... try this revision?
=COUNTIF(CHILDREN(Status22), ="Pass") + COUNTIF(CHILDREN(Status22), ="Not Applicable") / COUNT(CHILDREN(Status22))
You don't need all the SUM's cause the countif will automatically sum it for you and you're doing simple math to add and then divide each part.

Th formula you gave me did not work. It returned 700%.
However the following formula did work
=SUM(COUNTIF(CHILDREN(Status22), ="Pass") + COUNTIF(CHILDREN(Status22), ="Not Applicable")) / COUNT(CHILDREN(Status22))
Thank you!

Hmmm. Cool. Glad I helped you get on the right track! You're welcome!
Help Article Resources
Categories
Check out the Formula Handbook template!