Syntax resolution for IF/AND/CONTAINS

Options

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

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓
    Options

    @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

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓
    Options

    @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"

  • jjesmith
    jjesmith ✭✭✭✭
    Options

    Worked perfectly. Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!