COUNTIFS with CHILDREN Function and Multiple Criteria
I am trying to achieve the following a % Completion Formula using the following column: "Availability Status".
Availability Status has the following drop down: "Available" , "Unavailable - In Progress" , "Unavailable - Backlog"
I want to COUNTIFS the number of child rows that have "Available" and "Unavailable - In Progress" and divid that by all the children rows that also meet the "Available" and "Unavailable - In Progress" criteria.
I tried this:
=COUNTIFS(CHILDREN(), [Availability Status]:[Availability Status], "Available", [Availability Status]:[Availability Status], "Unavailable - In Progress") / COUNTIFS(CHILDREN(), [Availability Status]:[Availability Status], "Available", [Availability Status]:[Availability Status], "Unavailable - In Progress") * 100 + "%"
But, this did not work.
Any help would be much appreciated. Thanks!
Best Answer
-
@GRS Sure!
=COUNTIF(CHILDREN([Availability Status]@row), OR(@cell = "Available", @cell = "Unavailable - In Progress")) / COUNT(CHILDREN([Availability Status]@row))
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist | Process Improvement Enthusiast
Answers
-
Hi @GRS - Your formula would end up being 100% because your numerator and denominator have the same criteria. The formula below would count the cell if the status is either Available or Unavailable - In Progress (but not Backlog). Then, it would divide that count by all the children. You could change the cell to a % to avoid the final multiplication you had added.
=COUNTIF(CHILDREN(), OR(@cell = "Available", @cell = "Unavailable - In Progress")) / COUNT(CHILDREN())
Does this work for you?
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist | Process Improvement Enthusiast
-
@Amber Eakin - Thank you so much for the help
Could you show me how to reference the column name "Availability Status" in the above formula? I think I need to add that so the @cell works
Thank you!
-
@GRS Sure!
=COUNTIF(CHILDREN([Availability Status]@row), OR(@cell = "Available", @cell = "Unavailable - In Progress")) / COUNT(CHILDREN([Availability Status]@row))
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist | Process Improvement Enthusiast
-
thank you so much!!
-
@Amber Eakin - as a follow up to the above, here was my final formula:
COUNTIF(CHILDREN([Availability Status]@row), OR(@cell = "Available")) / COUNTIF(CHILDREN([Availability Status]@row), OR(@cell = "Available", @cell = "Unavailable - In Progress"))
I also want it to include any column that has a checkbox marked as complete. I have a column labeled "Complete" where you check the mark for completion - could you share how I could add that to the above formula?
Thanks!
-
Hi @Amber Eakin thought I would bump this as you were SO SO helpful - thanks so much for the help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 409 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!