OR function Not Working with Multiple Logical Expressions

Hello,

I have the following formula:

=IF(OR(Status4 <> "Complete", Status4 <> "Sent/Complete"), "not complete", "Complete")

which always returns "not complete", i.e. true.

However, modifying the expression so that only 1 logical expression is present, the expression works correctly, showing the true and false outcomes as expected. My question is, how do I modify this formula to work correctly and evaluate both logical expressions simultaneously.

Best Answer

Answers

  • Try :


    =IF(AND(Status4 <> "Complete", Status4 <> "Sent/Complete"), "not complete", "Complete")

  • Unfortunately, it still did not return the correct answer.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Fiona Painter

    Hi Fiona,

    I'm guessing that you don't want to show a status when the cell is empty.

    Try something like this.

    =IF(ISBLANK([email protected]); ""; IF(OR([email protected] <> "Complete"; [email protected] <> "Sent/Complete"); "not complete"; "Complete"))

    The same version but with the below changes for convenience.

    =IF(ISBLANK([email protected]), "", IF(OR([email protected] <> "Complete", [email protected] <> "Sent/Complete"), "not complete", "Complete"))

    Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

    Would that work?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    ✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Fiona,

    This should work for you - Anything other in the Status Column other than Complete or Sent/Complete will return Not Complete.

    =IF(NOT([Status]@row = "Complete"), IF(NOT([Status]@row = "Sent/Complete"), "Not Complete", "Complete"), "Complete")

    Rob

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Robert Gochee Your solution is actually a different version of @Eid E. Eid's solution and will have the same outputs.


    @Fiona Painter Are you able to provide more details? Can you write out what you expect to happen such as

    "If the Status is not "Complete" or not "Sent/Complete" then the output should be "not complete", otherwise it should be "Complete"."


    What are the other options for the Status column? If those two options are the only two containing the word "Complete" then we could use something like this...

    =IF(CONTAINS("Complete", [email protected]), "Complete", "not complete")


    My initial thought based on your post though would be the AND should have worked.

    thinkspi.com

  • @Paul Newcome,

    All 3 of these suggestions lead to the same results


  • Thanks for the tips everyone! I ended up adding some extra columns and breaking the formula down in each of those before parsing it together and got the answer that I was looking for.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Fiona Painter

    Excellent! Glad you got it working!

    I'm always happy to help!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.