Combine Multiple IF statements
Hi!
I am trying to write a formula that returns "Yes" when all expressions are true. I have written four IF formulas, that work independently but cannot figure out how to combine them. My final formula will not move past the first expression.
Here are my criteria:
IF Procurable is Yes, TBD, or Blank
=IF(OR([Procurable]@row = "YES", [Procurable]@row = "TBD", [Procurable]@row = ""), "Yes", "No")
IF Spend is greater than or equal to $100,000
=IF([Spend]@row >= 100000, "Yes", "No")
IF Contract Type 1 OR Contract Type 2 is not equal to Blank
=IF([Contract Type 1]@row <> "", "Yes", IF([Contract Type 2]@row <> "", "Yes", "No"))
IF Contract Expiration Date is greater than or equal to Today
=IF([Contract Expiration Date]@row >= TODAY(), "Yes", "No")
Final Formula
=IF(OR([Procurable]@row = "YES", [Procurable]@row = "TBD", [Procurable]@row = ""), "Yes", IF([Spend]@row >= 100000, "Yes", IF([Contract Type 1]@row <> "", "Yes", IF([Contract Type 2]@row <> "", "Yes", IF([Contract Expiration Date]@row >= TODAY(), "Yes", "No")))))
I saw another post that suggested utilizing "+" but I am not sure that I am writing the formula correctly. It is returning an odd "YesNo"
=IF(OR([Procurable]@row = "YES", [Procurable]@row = "TBD", [Procurable]@row = ""), "Yes") + IF([Spend]@row >= 100000, "Yes") + IF([Contract Type 1]@row <> "", "Yes", IF([Contract Type 2]@row <> "", "Yes") + IF([Contract Expiration Date]@row >= TODAY(), "Yes", "No"))
Any help is greatly appreciated.
Answers
-
Give this one a try:
=IF(AND(OR([Procurable]@row = "YES", [Procurable]@row = "TBD", [Procurable]@row = ""), [Spend]@row >= 100000, [Contract Type 1]@row <> "", [Contract Expiration Date]@row >= TODAY()), "YES", "NO")
This may help to visualize it… nesting IF/AND/OR can be a bit confusing:
-
Thanks so much for your response! It seems to be working.
I did add
=IF(AND(OR([Procurable]@row = "YES", [Procurable]@row = "TBD", [Procurable]@row = ""), [Spend]@row >= 100000, [Contract Type 1]@row <> "", [Contract Type 2]@row <> "", [Contract Expiration Date]@row >= TODAY()), "YES", "NO")
Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!