Formula help

Options

Hi

can anyone see what is wrong with my formula please?

The first formula says Incorrect argument

=IF(AND(CONTAINS("Remedial", Description@row)), (NOT(CONTAINS("R2", [Job Number]@row))), (NOT(CONTAINS("R3", [Job Number]@row))), (NOT(CONTAINS("R4", [Job Number]@row))), (NOT(CONTAINS("R5", [Job Number]@row))), (NOT(CONTAINS("R6", [Job Number]@row))), (NOT(CONTAINS("R7", [Job Number]@row))), (NOT(CONTAINS("R8", [Job Number]@row))), (NOT(CONTAINS("R9", [Job Number]@row))), (NOT(CONTAINS("R10", [Job Number]@row))), 1, 0)

I have also tried this but it doesn't work at all...

=IF(AND(CONTAINS("Remedial", Description@row), (NOT(CONTAINS("R2", [Job Number]@row)), (NOT(CONTAINS("R3", [Job Number]@row)), (NOT(CONTAINS("R4", [Job Number]@row)), (NOT(CONTAINS("R5", [Job Number]@row)),(NOT(CONTAINS("R6", [Job Number]@row)), (NOT(CONTAINS("R7", [Job Number]@row)),(NOT(CONTAINS("R8", [Job Number]@row)), (NOT(CONTAINS("R9", [Job Number]@row)), (NOT(CONTAINS("R10", [Job Number]@row)))))))))))), 1, 0)

The text in the Job Number@row cell looks like the numbers below but we only want to "grab" the numbers containing "R". NOTE: The Description@row discounts the number with the A in it so that has already been disgarded.

44799 R or 44023 R2 or 41589 A3

Thank you for your time and help.

Tracey

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    It looks like you may be having some parenthesis issues. Lets try cleaning those up first to see what happens.


    =IF(AND(CONTAINS("Remedial", Description@row), NOT(CONTAINS("R2", [Job Number]@row)), NOT(CONTAINS("R3", [Job Number]@row)), NOT(CONTAINS("R4", [Job Number]@row)), NOT(CONTAINS("R5", [Job Number]@row)), NOT(CONTAINS("R6", [Job Number]@row)), NOT(CONTAINS("R7", [Job Number]@row)), NOT(CONTAINS("R8", [Job Number]@row)), NOT(CONTAINS("R9", [Job Number]@row)), NOT(CONTAINS("R10", [Job Number]@row))), 1, 0)

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!