Multiple embedded IF/OR statements

AHANSON

Hi, I'm getting lost in my multiple IF/OR statements. I'm trying to write a formula that reads "if amendment = 7, then apply "yes" under specific conditions (any of them, thus the OR), and if amendment = 8, apply "yes" under different conditions.

Any help is appreciated. Below is my attempt:

=IF(OR(Amendment1 = "7", IF(OR([Visit #]@row = "C1D1", [Visit #]@row = "C2D1", [Visit #]@row = "C3D1", [Visit #]@row = "C4D1"), "Yes", "")), (Amendment1 = "8", IF(OR([Visit #]@row = "C1D2", [Visit #]@row = "C2D2", [Visit #]@row = "C3D2", [Visit #]@row = "C4D2")), "Yes", ""))

Amanda Alv

Hi,

I believe you were missing the "And" component to the formula. You want the cells that are equal to 7 AND one of the secondary items (which is the OR component). One other point is that you don't want a true/false statement if there is more than one if statement in the formula. In your first If statement, you had (.... "yes", "") as your output. That is telling the sheet to keep the cell blank if it is not true, however you still want the sheet to continue to the second part of your IF statement. You can see in the formula below that I only put a true answer for the first IF statement and a true/false answer at the very end. Hope that makes sense!

This formula should do it for you:

=IF(AND((OR([Visit #]@row = "C1D1", [Visit #]@row = "C2D1", [Visit #]@row = "C3D1", [Visit #]@row = "C4D1")), Amendment@row = 7), "Yes", IF(AND((OR([Visit #]@row = "C1D2", [Visit #]@row = "C2D2", [Visit #]@row = "C3D2", [Visit #]@row = "C4D2")), Amendment@row = 8), "Yes", ""))

Antonio Figueroa

You are not closing your OR Statements please see below.

=IF(OR(Amendment@row = 7, [Visit #]@row = "C1D1")

what you have is an incomplete statement

=IF(OR(Amendment1 = "7", IF(

Use formula below and you can add the remainder of the formula above to it butt follow the pattern

Below.

=IF(OR(Amendment@row = 7, [Visit #]@row = "C1D1"), IF(OR(Amendment@row = 7, [Visit #]@row = "C2D1"), IF(OR(Amendment@row = 7, [Visit #]@row = "C3D1"), "Yes", 0)))

AHANSON

Unfortunately that didn't work. I need the following

If amendment 7, fill in Yes if any of these conditions are met ([Visit@row = "C1D1" or "C2D1" or "C3D1"). Therefore, in the snapshot below, C1D1, C2D1 and C3D1 should all have "Yes" because they are all under amendment 7.

If amendment 8, fill in Yes if any of these conditions are met ([Visit@row = "C1D2" or "C2D2" or "C3D2").

The formula I wrote based on the answer above only provides "yes" if only 1 of those conditions are met

• =IF(OR(Amendment@row = "7", [Visit #]@row = "C1D1"), IF(OR(Amendment@row = "7", [Visit #]@row = "C2D1"), IF(OR(Amendment@row = "8", [Visit #]@row = "C1D2"), "Yes", "")))

Suggestions?

Amanda Alv

Hi,

I believe you were missing the "And" component to the formula. You want the cells that are equal to 7 AND one of the secondary items (which is the OR component). One other point is that you don't want a true/false statement if there is more than one if statement in the formula. In your first If statement, you had (.... "yes", "") as your output. That is telling the sheet to keep the cell blank if it is not true, however you still want the sheet to continue to the second part of your IF statement. You can see in the formula below that I only put a true answer for the first IF statement and a true/false answer at the very end. Hope that makes sense!

This formula should do it for you:

=IF(AND((OR([Visit #]@row = "C1D1", [Visit #]@row = "C2D1", [Visit #]@row = "C3D1", [Visit #]@row = "C4D1")), Amendment@row = 7), "Yes", IF(AND((OR([Visit #]@row = "C1D2", [Visit #]@row = "C2D2", [Visit #]@row = "C3D2", [Visit #]@row = "C4D2")), Amendment@row = 8), "Yes", ""))