Help! Nested If/AND/OR Formula's

Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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

Options

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

• ✭✭✭✭✭✭
Options

@Clark Usher Happy to help. 👍️

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

• Options

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”

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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.

• ✭✭✭✭✭✭
Options

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.

• Options

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.

• Options

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

Clark

• ✭✭✭✭✭✭
Options

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

• Options

Thanks so much - this is working 100%