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.

Tags:

Answers

  • Carson Penticuff
    Carson Penticuff Community Champion

    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:

  • Samantha Ludwig
    edited 01/20/25

    Hi @Carson Penticuff

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!