Help with if and and if and or statements

Options

Hello

I am trying to work out an if or and statement!

I want to only return DVCEE when either the first or the last part are correct. I have been dwelling on this for days! Please send help!

=IF(AND([ABC approval]@row = "ABC Approved", [Type]@row <> "SA", [Type]@row <> "ST", [Type]@row <> "SO")),

(IF(AND([Type]@row <> "SA", [Type]@row <> "ST", [Type]@row <> "SO", [DYWTR?]@row = "Yes", [R]@row = 0, [QRS]@row = "QRS Endorsed"), "GO FOR IT", ""))))


So I want to return something where both of the statements above are true, the "0" is an unchecked box. I have both above as one statement. They are all in colour, just return either blank completely (which isn't true) or "unparasable"

AS you can see, some of the parameters are the same for both, I managed the equation for one level lower, but cannot get this one to work!

Any help most appreciated!

Tags:

Answers

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    Options

    Is this what you're looking for:


    =IF(OR(AND([ABC approval]@row = "ABC Approved", Type@row <> "SA", Type@row <> "ST", Type@row <> "SO"), AND(Type@row <> "SA", Type@row <> "ST", Type@row <> "SO", DYWTR@row = "Yes", R@row = 0, QRS@row = "QRS Endorsed")), "GO FOR IT", "")

  • Global Engagement
    Options

    Thanks Michael, It made progress as I am now getting "#incorrect argument set.

  • Genevieve P.
    Options

    Hi @Global Engagement

    Michael's formula is structured correctly; is it possible that any of the referenced cells have an error, and that's being bubbled up into this formula?

    If not, could you post a screen capture of your sheet and the formula open in it? (But block out sensitive data)

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Global Engagement
    Options

    Thank you Genevieve, I have no doubt there is something I am doing wrong. I have triple checked everything, but no doubt it is something I am not aware of. By taking out the 'OR' it seems to have worked, but I havent fully tested to see if the argument holds.

    Thank you both for your help! Very much appreciated.

  • Genevieve P.
    Options

    Hi @Global Engagement

    If taking out the OR gave you a result, it sounds like perhaps there may be a closing parentheses missing in your version of the formula.

    Here's the structure broken down so you can see it a bit easier:


    =IF(OR(this, that), "Value if true", "Value if false")

    In your case, the two things to look at in your OR statement are both AND statements:


    =IF( OR( AND(--), AND(--) ), "Value if true", "Value if false")


    Let's break it out:


    =IF(

    OR(

    AND([ABC approval]@row = "ABC Approved", Type@row <> "SA", Type@row <> "ST", Type@row <> "SO"),

    AND(Type@row <> "SA", Type@row <> "ST", Type@row <> "SO", DYWTR@row = "Yes", R@row = 0, QRS@row = "QRS Endorsed")

    ),

    "GO FOR IT",

    "")


    Notice how after the second AND there are 2 closing parentheses? One for the AND and one for the OR? Let me know if this helped!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Global Engagement
    Options

    You are Amazing! Thank you for breaking it down for me. The second parenthesis was incorrect on mine. All fixed. Thank you both so much!

  • Genevieve P.
    Options

    Wonderful! I'm glad you got it working 🙂

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!