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
- 67K Get Help
- 441 Global Discussions
- 154 Industry Talk
- 502 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 156 Just for fun
- 79 Community Job Board
- 512 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am trying to calculate how many Open items a business analyst has that are in the critical category, this is the formula i am trying to use but i must be doing something wrong Open equals = To Do, I…
- I have a SUMIFS statement that I am trying to calculate a total of $ if it meets two criteria from another sheet. I am looking for it to meet the criteria of RRC Status: Engaged and RRC Pathway: Essen…
- After much research, I am still stumped. I have set up a Smartsheet that will allow users, via a form, request a date and time slot for hoteling (work desk reservation). I have a link in the form to t…