Multiple embedded IF/OR statements
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", ""))
Best Answer
-
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", ""))
Answers
-
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)))
-
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?
-
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", ""))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!