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?

Find more posts tagged with

Formulas

Comments

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

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

Hmmm. Cool. Glad I helped you get on the right track!

You're welcome!

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…