IF OR Formula

Options

Hello,

I need help with the following formula, it's working, but I need to change "IF(CONTAIN("Invoice#, [Job Description]@row), "INV")

=IF(OR(CONTAINS("Invoice#", [Job Description]@row), CONTAINS("CO#", [Job Description]@row), CONTAINS("Original Contract", [Job Description]@row), LEFT([Job Number]@row, 1) = 1, LEFT([Job Number]@row, 1) = 2, LEFT([Job Number]@row, 1) = 3), "JOB")

I need to modify to if contain "Invoice#" in Job Description row instead display "JOB", display "INV", the formula below is not working:

=IF(OR(CONTAINS("CO#", [Job Description]@row), CONTAINS("Original Contract", [Job Description]@row), LEFT([Job Number]@row, 1) = 1, LEFT([Job Number]@row, 1) = 2, LEFT([Job Number]@row, 1) = 3), "JOB"), CONTAINS("Invoice#", [Job Description]@row), "INV"))

Thank you.

Best Answer

  • JamesB
    JamesB ✭✭✭✭✭✭
    Answer ✓
    Options

    @BonW

    If I understand your request. The formula below should work. It first looks for invoice# and put "INV", if false it checks all your other conditions to input "JOB".

    =IF(CONTAINS("Invoice#", [Job Description]@row), "INV", IF(OR(CONTAINS("CO#", [Job Description]@row), CONTAINS("Original Contract", [Job Description]@row), LEFT([Job Number]@row, 1) = 1, LEFT([Job Number]@row, 1) = 2, LEFT([Job Number]@row, 1) = 3), "JOB", " "))

Answers

  • JamesB
    JamesB ✭✭✭✭✭✭
    Answer ✓
    Options

    @BonW

    If I understand your request. The formula below should work. It first looks for invoice# and put "INV", if false it checks all your other conditions to input "JOB".

    =IF(CONTAINS("Invoice#", [Job Description]@row), "INV", IF(OR(CONTAINS("CO#", [Job Description]@row), CONTAINS("Original Contract", [Job Description]@row), LEFT([Job Number]@row, 1) = 1, LEFT([Job Number]@row, 1) = 2, LEFT([Job Number]@row, 1) = 3), "JOB", " "))

  • BonW
    BonW ✭✭
    Options

    It work perfect! Thank you very much.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!