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?