Syntax resolution for IF/AND/CONTAINS
Please help with syntax error.
I'm trying to check a box if the following is true (in english):
IF TEXT is not in Project Long Name column AND Over 90 has content
OR
IF TEXT is in the Project Long Name column AND Over 180 has content
=IF([Over 90]@row <> “”, AND(NOT(CONTAINS("TEXT", [Project Long Name]@row))), OR([Over 180]@row <> “”, AND(CONTAINS(“TEXT”, [Project Long Name]))), 1, 0)
Best Answer
-
@jjesmith, try this...
=IF(OR(AND(NOT(CONTAINS("TEXT", [Project Long Name]@row)), [Over 90]@row <> ""), AND(CONTAINS("TEXT", [Project Long Name]@row), [Over 180]@row <> "")), 1, 0)
You were correct to use OR() and HAS().
Check the box when either case1 or case2 is true, i.e., OR(case1, case2) = true. Expressed as...
IF(OR(case1, case2), 1, 0)
case1 = AND(NOT(CONTAINS("TEXT",[Project Long Name]@row)), [Over 90]@row <> "")
case2 = AND(CONTAINS("TEXT",[Project Long Name]@row), [Over 180]@row <> "")
You may want to use HAS() if you're looking for the whole word. Your CONTAINS() expression will evaluate to true if "text" appears anywhere in the field. E.g., "Context", "Textile"
Answers
-
@jjesmith, try this...
=IF(OR(AND(NOT(CONTAINS("TEXT", [Project Long Name]@row)), [Over 90]@row <> ""), AND(CONTAINS("TEXT", [Project Long Name]@row), [Over 180]@row <> "")), 1, 0)
You were correct to use OR() and HAS().
Check the box when either case1 or case2 is true, i.e., OR(case1, case2) = true. Expressed as...
IF(OR(case1, case2), 1, 0)
case1 = AND(NOT(CONTAINS("TEXT",[Project Long Name]@row)), [Over 90]@row <> "")
case2 = AND(CONTAINS("TEXT",[Project Long Name]@row), [Over 180]@row <> "")
You may want to use HAS() if you're looking for the whole word. Your CONTAINS() expression will evaluate to true if "text" appears anywhere in the field. E.g., "Context", "Textile"
-
Worked perfectly. Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!