Nested IF Formula Troubles

We are trying to use a nested IF formula to use two different sets of criteria to return two different results. I feel we are close because we get an Incorrect Argument error, rather than the dreaded Unparsable, but I am not seeing what we are missing. We also tried adding a "" as a return if False value but that didn't have any impact.

=IF(Status@row, OR(@cell = "Active", @cell = "Pre-Go Live", @cell = "Closing", @cell = "Pipeline - Active"), "Active", IF(Status@row, OR(@cell = "Pipeline - 0 thru 2", @cell = "Pipeline - 3. Finalist", @cell = "Pipeline - 4. Partner of Choice", @cell = "Pipeline - 5. Selected"), "Forecast"))

We want to use this as a column formula in a Capacity Status column to show a project as Active or Forecast based on what is listed in our Status column. The Status column is a single select column so we shouldn't need HAS(@cell. Do we need to check our column properties too? Thank you!

Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Tina Rustvold

    The syntax of your OR statements aren't what smartsheet is expecting

    =IF(OR(Status@row = "Active", Status@row = "Pre-Go Live", Status@row = "Closing", Status@row = "Pipeline - Active"), "Active", IF( OR(Status@row= "Pipeline - 0 thru 2", Status@row= "Pipeline - 3. Finalist", Status@row = "Pipeline - 4. Partner of Choice", Status@row = "Pipeline - 5. Selected"), "Forecast"))

    will this work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Tina Rustvold

    The syntax of your OR statements aren't what smartsheet is expecting

    =IF(OR(Status@row = "Active", Status@row = "Pre-Go Live", Status@row = "Closing", Status@row = "Pipeline - Active"), "Active", IF( OR(Status@row= "Pipeline - 0 thru 2", Status@row= "Pipeline - 3. Finalist", Status@row = "Pipeline - 4. Partner of Choice", Status@row = "Pipeline - 5. Selected"), "Forecast"))

    will this work for you?

    Kelly

  • heyjay
    heyjay ✭✭✭✭✭
    =IF(OR
    Status@row = "Active", 
    Status@row = "Pre-Go Live", 
    Status@row = "Closing", 
    Status@row = "Pipeline - Active"), 
    "Active", 
    
    IF(OR(
    Status@row = "Pipeline - 0 thru 2", 
    Status@row = "Pipeline - 3. Finalist", 
    Status@row = "Pipeline - 4. Partner of Choice", 
    Status@row = "Pipeline - 5. Selected"), 
    "Forecast"))
    

    ...

  • Tina Rustvold
    Tina Rustvold ✭✭✭✭✭

    Thanks so much @Kelly Moore and @heyjay--that did the trick. I wondered if that was where the glitch was, but Friday brain power wasn't getting there.

  • Tina Rustvold
    Tina Rustvold ✭✭✭✭✭

    Since you all were such a help with this, I am coming back for how to add an additional IF. We only want something returned if it is for an active project so I tried adding a just an IF, then an IF(OR, then an IF(AND but still get an incorrect argument error. Additionally, I have tried IM Done = 0 and not included the "" and even puting this criteria a the beginning of the formula. Suggestions?

    =IF(OR(Status@row = "Active", Status@row = "Pre-Go Live", Status@row = "Closing", Status@row = "Pipeline Active", "Active", IF(OR(Status@row = "Pipeline - 0 thru 2", Status@row = "Pipeline - 3. Finalist", Status@row = "Pipeline - 4. Partner of Choice", Status@row = "Pipeline - 5. Selected", IF(AND([IM Done]@row, 0, ""))))))

    BTW the IM Done is a checkbox column--and Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!