If/And/Or formula help, please!

Alejandra Orihuela

Hi there,

I've been playing around with an if/and/or formula but I can't get it to work. Can someone please review my formula and tell me what I'm doing wrong?

What the formula should do is to indicate "Yes" if status of row X and row Y is any combination of "Yes" or "N/A". If there is a "No" in any either of those rows, the result of the formula should be "No". For example, row X = "Yes" and row Y = "N/A", the formula should result in a "Yes". However, something is wrong because all "Yes" and/or "N/A" combinations are yielding a "No" result :(

This is the formula I came up with =IF(AND(OR([All Required Documents Shared via Sherlock]2 = "Yes", [All Required Documents Shared via Sherlock]2 = "N/A"), [Comp Tool Attached]2 = "Yes", [Comp Tool Attached]2 = "N/A"), "Yes", "No")

Thanks in advance!!

David Tutwiler

There are many ways to do this, but give your example, I think you're missing one more OR statement on the last check. So it would read:

=IF(AND(OR([All Required Documents Shared via Sherlock]@row = "Yes", [All Required Documents Shared via Sherlock]@row = "N/A"), OR([Comp Tool Attached]@row = "Yes", [Comp Tool Attached]@row = "N/A")), "Yes", "No")

Without that OR, it would require that the Comp Tool column would equal both Yes and N/A to solve for True. Also, I changed the row number (2) to @row.

I think you could simplify the formula if you looked for No instead of Yes/N/A, but I still think the above formula will work.

David Tutwiler

There are many ways to do this, but give your example, I think you're missing one more OR statement on the last check. So it would read:

=IF(AND(OR([All Required Documents Shared via Sherlock]@row = "Yes", [All Required Documents Shared via Sherlock]@row = "N/A"), OR([Comp Tool Attached]@row = "Yes", [Comp Tool Attached]@row = "N/A")), "Yes", "No")

Without that OR, it would require that the Comp Tool column would equal both Yes and N/A to solve for True. Also, I changed the row number (2) to @row.

I think you could simplify the formula if you looked for No instead of Yes/N/A, but I still think the above formula will work.

Alejandra Orihuela

Thank you, David! That worked! And you're right, the formula would be simpler if I looked at "No" only. I hadn't thought about that. I going to use this formula instead =IF(OR([All Required Documents Shared via Sherlock]@row = "No", [Comp Tool Attached]@row = "No"), "No", "Yes").

Thank you for making my day :D

David Tutwiler

Glad it worked!