Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Nested IF Formula Troubles

    ✭✭✭✭✭
    in Formulas and Functions

    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:
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • Community Champion
      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

      · Share on FacebookShare on Twitter

    Answers

    • Community Champion
      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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭ 
      =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"))

      ...

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      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.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      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!

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions