Help! Nested If/AND/OR Formula's

Clark Usher

Hi - I am trying to write a nested formula that evaluates multiple criteria to returns a "Yes" or "No"

Basically I need to assess the status of a task, based on values in multiple columns and state "yes" complete or "no" its not complete in the All Reviews Done column.

These are my Column Names

The criteria is in the right side column.

How do I nested =if(and with OR statements?

I've tried lots of combinations

=IF(AND([Primary Review: Done, Need Info, Problem or N/A]@row = "Green", [Back-Up Reviewer]@row = 0), "Yes"), OR(AND([Primary Review: Done, Need Info, Problem or N/A]@row = "Green", [Back-Up Review: Done, Need Info, Problem, N/A]@row = "Green"), "Yes"), OR(AND([Primary Review: Done, Need Info, Problem or N/A]@row = "Green", [Back-Up Review: Done, Need Info, Problem, N/A]@row = "Gray"), "Yes"), IF([Primary Review: Done, Need Info, Problem or N/A]@row = "Gray"), "Yes", "No"))

Paul Newcome

Try this:

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

Paul Newcome

Try this:

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

Clark Usher

AWESOME - this meets all the criteria and works perfectly.

Hopefully they don't change the criteria again or I'll be stumped again!

Thanks so much for your help, my first time using this community!

Really appreciated

Genevieve P.

@Clark Usher - If you get stumped again, you can always come back! 🙂

Paul Newcome

@Clark Usher Happy to help. 👍️

And as @Genevieve P. said... You can always come back for another visit if you get stumped.

Clark Usher

Hi @Paul Newcome, they did move the criteria on me today, I rewrote it below if this makes sense?

I tried to add it into your formula but I am still making mistakes somewhere.

Conditions if Back-Up Reviewer = unassigned (0) and

If Primary Review = “Green” or “Gray”, “Yes”

Conditions if Back-Up Reviewer = assigned (1) and:

If Primary Review = “Green” and Back-Up Review = “Green”, “Yes”

If Primary Review = “Green” and Back-Up Review = “Gray”, “Yes”

If Primary Review = “Gray” and Back-Up Review = “Green”, “Yes”

If Primary Review = “Gray” and Back-Up Review = “Gray”, “Yes”

If false, “No”

Paul Newcome

I would use this:

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

Clark Usher

Hi - thanks I really appreciate your help here, I am sorry to keep coming back to you.

When I use this with Back-Up Reviewer = assigned, I get #Incorrect Argument Set

Paul Newcome

Is it possible to have a back-up with no primary?

Clark Usher

Hi - No, there will always be a Primary if there is a Back-Up or a Primary only (no back-up).

Never just a back-up.

Paul Newcome

Ok. So in your screenshot it looks like you have a back-up with no primary. Try plugging in a Primary to see if it will work.

Clark Usher

Sorry the shot wasn't wide enough, there was a primary reviewer but I don't see that column referenced in the formula. I had thought it might just have been a column naming issue.

Clark Usher

Sorry to bug you @Paul Newcome, any chance you were able to review this for me.

Thanks in advance.

Clark

Paul Newcome

Sorry about that. Misplaced parenthesis.

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

Clark Usher

Thanks so much - this is working 100%

REALLY appreciate your help here.

Thanks Clark

Paul Newcome

Happy to help. 👍️