If(And or If(Or for multiple criteria in a dropdown box

I have a dropdown box that allows multiple value selection. I know how to build an if statement if there are individual criteria selected but I am trying to write a statement if all values are selected. I've only been working on building the formula that will select all three. I'm not sure if I'm missing something but it will not produce any results.

I appreciate any help. Thanks!

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @Amy Mosley,

    Your formula would look something like this for your example:

    =IF(AND(HAS([Entry Door]@row, "Door Kicked In"), HAS([Entry Door]@row, "Door Broken"), HAS([Entry Door]@row, "Master Key Not Working")), "$500 & Access Control")

    In the event of the criteria not being met it would show nothing, but I am guessing you would probably expand this into a nested IF statement for additional situations.

    Hope this helps, but if you've any other questions etc. then just post! 😊

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @Amy Mosley,

    Your formula would look something like this for your example:

    =IF(AND(HAS([Entry Door]@row, "Door Kicked In"), HAS([Entry Door]@row, "Door Broken"), HAS([Entry Door]@row, "Master Key Not Working")), "$500 & Access Control")

    In the event of the criteria not being met it would show nothing, but I am guessing you would probably expand this into a nested IF statement for additional situations.

    Hope this helps, but if you've any other questions etc. then just post! 😊

  • Amy Mosley
    Amy Mosley ✭✭✭
    edited 03/09/23

    Thank you so much for this!

    I was able to make it work for all three values being checked but, if there are only 1 or two being checked, it's blank. So, I duplicated the formula that you provided and added it to the end with only 2 criteria and it say #INCORRECT ARGUMENT. Should I put in OR or AND for the second half? Or am I way off all together?


  • Amy Mosley
    Amy Mosley ✭✭✭

    @Nick Korna - I was able to fix it. Thank you so much for your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!