Using COUNTIF and OR Function

TroyT

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

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")

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")

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.