Formula using Sum and Countif not returning expected outcomes

MTaylor
MTaylor ✭✭
edited 12/09/19 in Formulas and Functions

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?

Capture.PNG

Capture.PNG

Tags:

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    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. 

  • MTaylor
    MTaylor ✭✭

    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!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Hmmm. Cool. Glad I helped you get on the right track! :) You're welcome!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!