Using COUNTIF and OR Function

i am currently using the following formula to control a parent reporting "Ready" or "Not Ready":

=IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "COMPLETE"), "COMPLETE", "Not Ready")

I would like to add an "OR" function to this to allow both "COMPLETE" and "NOT REQUIRED" to trigger the parent "Ready". Can you help me understand how to insert the "OR" function, i dont understand how the syntax needs to work for this... Thanks!

Best Regards,

Troy

Find more posts tagged with

Formulas

Accepted answers

Jeff Reisman

@TroyT

Probably the least complicated syntax-wise would be to use two completely separate equations in the first part of the IF:

=IF(OR(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "COMPLETE"), COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "NOT REQUIRED")), "COMPLETE", "Not Ready")

With this syntax you're saying IF either of these two equations is true, then set the value of "COMPLETE", otherwise set the value of "Not Ready".

HOWEVER - what I think you really want to do from a logical standpoint is to add the Count of Child rows showing COMPLETE to the count of Child rows showing NOT REQUIRED, and check if that equals the count of all child rows. In that case you don't need an OR:

=IF(COUNT(CHILDREN()) = (COUNTIF(CHILDREN(), "COMPLETE") + COUNTIF(CHILDREN(), "NOT REQUIRED")), "COMPLETE", "Not Ready")

All comments

Jeff Reisman

@TroyT

Probably the least complicated syntax-wise would be to use two completely separate equations in the first part of the IF:

=IF(OR(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "COMPLETE"), COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "NOT REQUIRED")), "COMPLETE", "Not Ready")

With this syntax you're saying IF either of these two equations is true, then set the value of "COMPLETE", otherwise set the value of "Not Ready".

=IF(COUNT(CHILDREN()) = (COUNTIF(CHILDREN(), "COMPLETE") + COUNTIF(CHILDREN(), "NOT REQUIRED")), "COMPLETE", "Not Ready")

TroyT

Jeff,

Thank you so much for the response, and in fact both cases are true. Your first response is perfect for my current problem, however i have another application for the comparison equation you provided!

thank you again for crushing it @Jeff Reisman

Jeff Reisman

Glad it worked for you. Have a great day.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.