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

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    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

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭

    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

  • GRS
    GRS ✭✭

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

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    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

  • GRS
    GRS ✭✭

    thank you so much!!

  • GRS
    GRS ✭✭

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

  • GRS
    GRS ✭✭

    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!