Help! Nested If/AND/OR Formula's

Clark Usher

Hi, @Paul Newcome, looking for your expertise once again!

How would I add another column (Legal's Review) into this that takes the same considerations as Primary's Review & Back-Up's Review?

Meaning, the Global Review Status would only return "Green" if the Primary's Review was "Green or Gray", the Back-Up's Review was "Green or Gray" & the Legal's Review was "Green or "n/a" "

All other combination would return the Global Status to "Red"

=IF([Back-Up Reviewer]@row = "", IF(OR([Primary Review]@row = "Green", [Primary Review]@row = "Gray"), "Yes", "No"), IF(COUNTIFS([Primary Review]@row:[Back-Up Review]@row, OR(@cell = "Green", @cell = "Gray")) = 2, "Yes", "No"))

Paul Newcome

@Clark Usher Try changing

[Back-Up's Review]

to

[Legal's Review]

and then change the 2 to a 3.

Clark Usher

Thanks for the response.

It is taking the Primary's Review as a priority and ignoring the others. As in, if the Primary's Review is Green or Gray the Global Review Status changes to Green, ignoring the Back-Up's Review or Legal's Review inputs, even if they are Red.

=IF([Back-Up Reviewer Name]@row = "", IF(OR([Primary's Review]@row = "Green", [Primary's Review]@row = "Gray"), "Green", "Red"), IF(COUNTIFS([Primary's Review]@row:[Legal's Review]@row, OR(@ )) = 3, "Green", "Red"))

Paul Newcome

That's how it was working prior to adding in the extra column. Is that not the logic you want now?

Clark Usher

This is the original one you did and it works perfect, I just need the same logic but for a third column (Legal's Review) to be added for the output in the Global Review Status.

=IF([Back-Up Reviewer]@row = "", IF(OR([Primary's Review]@row = "Green", [Primary's Review]@row = "Gray"), "Green", "Red"), IF(COUNTIFS([Primary's Review]@row:[Back-Up's Review]@row, OR(@ )) = 2, "Green", "Red"))

Paul Newcome

If it is the same logic, it looks like you are just missing the piece that goes inside of the OR function.

Clark Usher

Sorry the previous one didn't paste correctly, this one is the working one that considers Primary's and Back-Up's Review

=IF([Back-Up Reviewer]@row = "", IF(OR([Primary's Review]@row = "Green", [Primary's Review]@row = "Gray"), "Green", "Red"), IF(COUNTIFS([Primary's Review]@row:[Back-Up's Review]@row, OR(@cell= "green", "@cell = "Gray")) = 2, "Green", "Red"))

If I want to add the third column with the same considerations, Legal's Review, I think this is what you suggested before, but its not working.

=IF([Back-Up Reviewer]@row = "", IF(OR([Primary's Review]@row = "Green", [Primary's Review]@row = "Gray"), "Green", "Red"), IF(COUNTIFS([Primary's Review]@row:[Legal's Review]@row, OR(@cell= "green", "@cell = "Gray")) = 3, "Green", "Red"))

Sorry, I have limited knowledge on this level of complexity.

thanks in advance

Paul Newcome

I see what I missed. Will there always be an input in the Legal's Review column, or is it like the Back-Up Reviewer column where it may or may not be empty?

Clark Usher

yeh, It may or may not be empty.

Paul Newcome

Ok. Try this then:

=IF(AND(OR([Primary Review]@row = "Green", [Primary Review]@row = "Gray"), OR([Back-Up Review]@row = "Green", [Back-Up Review]@row = "Gray", [Back-Up Review]@row = ""), OR([Legal's Review]@row = "Green", [Legal's Review]@row = "N/A", [Legal's Review]@row = "")), "Green", "Red")

Clark Usher

Perfect, this works. Thanks for your help again!

Paul Newcome

Happy to help. 👍️