COUNTIF(CHILDREN() with AND for multiple condition check

SBell

Hi Folks

I'm brain fogged as I've been putting together a nest of IFs to perform status roll up values to a parent off the values of the children and am hoping someone has a thought.

I'm spinning on one bit that checks if the count of Late status is less then 0 AND count of Delayed is greater than 0. If true, then it should be Delayed. If it's False then it'll drop top the next line that checks count of Late is greater than 0

It is a long formula that is working, here is a snip of the section, in bold is the issue;

...

IF(COUNTIF(CHILDREN(), "Canceled")+COUNTIF(CHILDREN(), "Not Applicable")+COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started",

IF(COUNTIF(CHILDREN(), "Canceled")+COUNTIF(CHILDREN(), "Complete")+COUNTIF(CHILDREN(), "Not Applicable")+COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "In Progress",

IF(AND(COUNTIF(CHILDREN(), "Late")<0,(COUNTIF(CHILDREN(), "Delayed")>0,"Delayed",

IF(COUNTIF(CHILDREN(), "Late")>0,"Late",

IF(COUNTIF(CHILDREN(), "In Progress")>0,"In Progress","---")))))))))))))))))))))))

Thank you,

Shawn

Paul Newcome

Why not adjust the order? If you put the count of Late being greater than zero first, then by default it must be less than or equal to zero for anything after.

.................IF(COUNTIFS(CHILDREN(), "Late") > 0, "Late", IF(COUNTIFS(CHILDREN(), "Delayed") > 0, "Delayed", ..................

If you can prioritize the different statuses then you should be able to use this logic for the majority of the outputs. For example... If there is even one "Late" and you want that to be the absolute priority regardless of the other children, then putting that first means you can leave it out of everything after it.

Paul Newcome

Why not adjust the order? If you put the count of Late being greater than zero first, then by default it must be less than or equal to zero for anything after.

.................IF(COUNTIFS(CHILDREN(), "Late") > 0, "Late", IF(COUNTIFS(CHILDREN(), "Delayed") > 0, "Delayed", ..................

If you can prioritize the different statuses then you should be able to use this logic for the majority of the outputs. For example... If there is even one "Late" and you want that to be the absolute priority regardless of the other children, then putting that first means you can leave it out of everything after it.

SBell

Hi Paul, of course. Good grief, thank you for the fresh set of eyes and suggestion. Exactly what I needed.

Shawn

Paul Newcome

Happy to help. 👍️