Percentage complete with multiple criteria for children

Namaste03

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.

Paul Newcome

Try this:

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

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))

1. The range in my formula is: CHILDREN(Status@row),
2. 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. 👍️