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!

Find more posts tagged with

Accepted answers

Amber Eakin

@GRS Sure!

=COUNTIF(CHILDREN([Availability Status]@row), OR(@cell = "Available", @cell = "Unavailable - In Progress")) / COUNT(CHILDREN([Availability Status]@row))

All comments

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?

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

@GRS Sure!

=COUNTIF(CHILDREN([Availability Status]@row), OR(@cell = "Available", @cell = "Unavailable - In Progress")) / COUNT(CHILDREN([Availability Status]@row))

GRS

thank you so much!!

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.