Percentage complete with multiple criteria for children

I have child rows with a drop-down that have the following options in the "Status" column: Complete, Not Started, In Progress, Blocked, N/A.

In the "% Complete" column, I currently have a working formula that updates the percentage complete if a row is marked as "Complete". I need the formula to also update the "% Complete" column if "N/A" is selected. I need to have N/A as an option for our records.

I have tried working with "COUNTIFS" and "OR" but I cannot seem to get my function working. Any help is greatly appreciated.

Find more posts tagged with

Accepted answers

Paul Newcome

Try this:

=COUNTIF(CHILDREN(Status@row), OR(@cell="Complete", @cell = "N/A")) / COUNT(CHILDREN(Status@row))

All comments

Paul Newcome

Try adjusting the criteria portion of your COUNTIF to

OR(@cell = "Complete", @cell = "N/A")

Namaste03

I tried the following with no success:

1st try: =COUNTIF((CHILDREN(Status@row), ="Complete") OR (Status@row), = "N/A") / COUNT(CHILDREN(Status@row))

2nd attempt: =COUNTIF((CHILDREN(Status@row), ="Complete") OR (CHILDREN(Status@row), = "N/A")) / COUNT(CHILDREN(Status@row))

Paul Newcome

Try setting the criteria within the COUNTIF to exactly as I have it in my example.

=COUNTIF(range, OR(......))

Namaste03

Here is what I currently understand about the formula I am using below:

=COUNTIF(CHILDREN(Status@row), ="Complete") / COUNT(CHILDREN(Status@row))

The range in my formula is: CHILDREN(Status@row),
The criterion is: ="Complete"

The formula would then be:

=COUNTIF(CHILDREN(Status@row), OR (Status@row = "Complete", Status@row = "N/A") / COUNT (CHILDREN(Status@row))

I received the error #UNPARSEABLE

Paul Newcome

Try this:

=COUNTIF(CHILDREN(Status@row), OR(@cell="Complete", @cell = "N/A")) / COUNT(CHILDREN(Status@row))

Namaste03

Thank you for the help, Paul. That worked and I was able to use the formula to also count the number of tasks as well!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.