# COUNTIFS with CHILDREN Function and Multiple Criteria

Options
✭✭

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!

• ✭✭✭✭✭✭
Options

@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

• ✭✭✭✭✭✭
Options

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

• ✭✭
Options

@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!

• ✭✭✭✭✭✭
Options

@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

• ✭✭
Options

thank you so much!!

• ✭✭
Options

@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!

• ✭✭
Options

Hi @Amber Eakin thought I would bump this as you were SO SO helpful - thanks so much for the help!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!