IF AND NOT CONTAINS
I am trying to create a category of time clocks using the info in a comments section. The Comments are as follows:
IT31 Bio (suprema)
DeviceID: US25091
AssistIT: IWA70
MAC: 0001ce0199ea
IP:DHCP
Timezone: ET
Netinstall ID: 200735
Netinstall PIN: 2735
Punch Scheme: IN, OUT, INL, CBI

I'm hoping for these results (essentially, either the time clock is Biometric or standard and then is either WiFi or Ethernet but the formula below is not working. I've tried various versions, including the AND statement up front, etc.
If the comments contain "Bio" and "Wifi" then "NXG G2+ Biometric WiFi" else
If the comments contain "Bio" but do not include "Wifi" then "NXG G2+ Biometric Ethernet" else
If the comments do not contain "Bio" but do include "Wifi" then "NXG G2+ WiFi" else "NXG G2+ Ethernet"
=IF(AND(CONTAINS("Bio", Comments@row), CONTAINS("Wifi", Comments@row)), "NXG G2+ Biometric WiFi", IF(CONTAINS("Bio", Comments@row), (AND(NOT(CONTAINS("Wifi", Comments@row))), "NXG G2+ Biometric Ethernet", IF(NOT(CONTAINS("Bio", Comments@row), AND(CONTAINS "Wifi", Comments@row), "NXG G2+ WiFi", "NXG G2+ Ethernet"))).
Please help.
Best Answer

A couple of rules  when using AND inside an IF, remember that you need to include every criteria you want to evaluate into a single logical statement. All criteria being considered by AND need to reside inside a surrounding set of parentheses and behind a single comma designating that section as the logical statement to evaluate.
=IF(AND( logical statement1, logical statement 2 ), value if true, value if false )
Your first IF looks great. With the second and third, I think the NOT is throwing you off with your syntax. Let's fix those:
=IF(AND(CONTAINS("Bio", Comments@row), CONTAINS("Wifi", Comments@row)), "NXG G2+ Biometric WiFi", IF(AND(CONTAINS("Bio", Comments@row), NOT(CONTAINS("Wifi", Comments@row))), "NXG G2+ Biometric Ethernet", IF(AND(CONTAINS("Wifi", Comments@row), NOT(CONTAINS("Bio", Comments@row))), "NXG G2+ WiFi", "NXG G2+ Ethernet")))
The above should work for you.
If something isn't working, first check the colorcoding on the parentheses. Make sure each open parentheses has a matching close parentheses in the right place. If the colorcoding is good, then take each of your IFs and test them independently to verify they work. Between those two things, you should be able to determine where the problem lies.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers

A couple of rules  when using AND inside an IF, remember that you need to include every criteria you want to evaluate into a single logical statement. All criteria being considered by AND need to reside inside a surrounding set of parentheses and behind a single comma designating that section as the logical statement to evaluate.
=IF(AND( logical statement1, logical statement 2 ), value if true, value if false )
Your first IF looks great. With the second and third, I think the NOT is throwing you off with your syntax. Let's fix those:
=IF(AND(CONTAINS("Bio", Comments@row), CONTAINS("Wifi", Comments@row)), "NXG G2+ Biometric WiFi", IF(AND(CONTAINS("Bio", Comments@row), NOT(CONTAINS("Wifi", Comments@row))), "NXG G2+ Biometric Ethernet", IF(AND(CONTAINS("Wifi", Comments@row), NOT(CONTAINS("Bio", Comments@row))), "NXG G2+ WiFi", "NXG G2+ Ethernet")))
The above should work for you.
If something isn't working, first check the colorcoding on the parentheses. Make sure each open parentheses has a matching close parentheses in the right place. If the colorcoding is good, then take each of your IFs and test them independently to verify they work. Between those two things, you should be able to determine where the problem lies.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Thank you thank you thank you! I typically test one IF at a time and it would work but then when I tried to add more, well...
Thank you for the explanation and for the sample. It worked!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 352 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 443 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!