Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
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.
Best Answer
-
Try this:
=COUNTIF(CHILDREN(Status@row), OR(@cell="Complete", @cell = "N/A")) / COUNT(CHILDREN(Status@row))
Answers
-
Try adjusting the criteria portion of your COUNTIF to
OR(@cell = "Complete", @cell = "N/A")
-
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))
-
Try setting the criteria within the COUNTIF to exactly as I have it in my example.
=COUNTIF(range, OR(......))
-
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
-
Try this:
=COUNTIF(CHILDREN(Status@row), OR(@cell="Complete", @cell = "N/A")) / COUNT(CHILDREN(Status@row))
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 501 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 79 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I have looked through all of the answers for this topic, and still cannot find a solution that works for me. I have a sheet with a multi-select dropdown used to code responses. In another sheet, I wan…
- I have 1 sheet that has duplicated Classroom IDs but different scores on each line. I need to flag any instances during the same term of a difference over 1.00 higher or lower with the same Classroom …
- I have two formulas that are fairly identical but one is producing a #NO MATCH error. =COUNTIFS(Shop:Shop,Shop@row,[Review Start Date]:[Review Start Date], YEAR(@cell)= YEAR([Review Start Date]@row)) …