IF OR Formula Help!

Options

Hello!

I'm trying to put together a formula in the Posted To field pictured below, to return a result of either "Internal" or "External." The formula I'm using is below.

=IF(OR([Portal Status]@row = "Posted", [ATS Posted]@row = "internal"), "Internal", "External")

This formula returns an "Internal" result but I can't quite figure out how to get it to return an "External" result or a blank result.


 

What I'm looking to accomplish is the following... 

 If the Portal Status = Posted AND the ATS Posted = Internal, then the Posted To column should read "Internal" 

 If the Portal Status = Posted AND the ATS Posted = Careers, then the Posted To column should read "External" 

 If the Portal Status = Posted AND the ATS Posted = Web Post: Broadbean, then the Posted To column should read "External" 

If the Portal Status = Canceled AND the ATS Posted= Internal, then the Posted To column should read "Not Posted"

If the Portal Status = Canceled AND the ATS Posted= External, then the Posted To column should read "Not Posted"

 If the Portal Status = Canceled AND the ATS Posted = Web Post: Broadbean, then the Posted To column should read "Canceled" 

If the Portal Status = BLANK AND the ATS Posted= BLANK, then the Posted To column should read "BLANK"

Any suggestions are much appreciated. 😊

Tags:

Best Answers

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Answer ✓
    Options

    Hi @Beecher2019,

    See if this works for you.

    =IF(AND([Portal Status]@row = "Posted", [ATS Posted]@row = "Internal"), "Internal", IF(AND([Portal Status]@row = "Posted", OR([ATS Posted]@row = "Careers", [ATS Posted]@row = "Web Post: Broadbean")), "External", IF(AND([Portal Status]@row = "Canceled", OR([ATS Posted]@row = "Internal", [ATS Posted]@row = "External")), "Not Posted", IF(AND([Portal Status]@row = "Canceled", [ATS Posted]@row = "Web Post: Broadbean"), "Canceled"))))

    Hope this helps,

    Dave

  • Beecher2019
    Beecher2019 ✭✭
    Answer ✓
    Options

    Yes! Thank worked, thank you!! Happy Holidays. ☺️

Answers

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Answer ✓
    Options

    Hi @Beecher2019,

    See if this works for you.

    =IF(AND([Portal Status]@row = "Posted", [ATS Posted]@row = "Internal"), "Internal", IF(AND([Portal Status]@row = "Posted", OR([ATS Posted]@row = "Careers", [ATS Posted]@row = "Web Post: Broadbean")), "External", IF(AND([Portal Status]@row = "Canceled", OR([ATS Posted]@row = "Internal", [ATS Posted]@row = "External")), "Not Posted", IF(AND([Portal Status]@row = "Canceled", [ATS Posted]@row = "Web Post: Broadbean"), "Canceled"))))

    Hope this helps,

    Dave

  • Beecher2019
    Beecher2019 ✭✭
    Answer ✓
    Options

    Yes! Thank worked, thank you!! Happy Holidays. ☺️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!