IF AND NOT CONTAINS

JackieKim

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: US2509-1

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 Wi-Fi" 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+ Wi-Fi" else "NXG G2+ Ethernet"

=IF(AND(CONTAINS("Bio", Comments@row), CONTAINS("Wifi", Comments@row)), "NXG G2+ Biometric Wi-Fi", 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+ Wi-Fi", "NXG G2+ Ethernet"))).

Please help.

Jeff Reisman

@JackieKim

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 Wi-Fi", 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+ Wi-Fi", "NXG G2+ Ethernet")))

The above should work for you.

If something isn't working, first check the color-coding on the parentheses. Make sure each open parentheses has a matching close parentheses in the right place. If the color-coding 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.

Jeff Reisman

@JackieKim

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 Wi-Fi", 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+ Wi-Fi", "NXG G2+ Ethernet")))

The above should work for you.

If something isn't working, first check the color-coding on the parentheses. Make sure each open parentheses has a matching close parentheses in the right place. If the color-coding 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.

JackieKim

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!