IF AND Formula for 5 Options

Hey Community, please help me figure this out.

We have 5 options for types of projects and they are the following:

Technical Services Projects

Technical Services Programs

Enterprise Projects

Enterprise Programs

ODE


We are trying to output one of those five options based on the columns Project Type and Program Type.

Project type can be either Technical Services Projects or Enterprise Projects, while Program Type can be either Technical Services Programs or Enterprise Programs. The last option (assuming this would be the false part of the IF statement) is ODE.

See picture below to get an idea of the columns, we are trying to output it in the OPPTY Type Master column (ignore the red row).

Any help on this is greatly appreciated!!

Tags:

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @John- Michael Diedrich

    So if I understand correctly, they select one of two values from "Project Type" column, OR they select one of two values from "Program Type" column, and you want whichever one thing they've chosen to be displayed in "OPPTY Type Master" column, unless they've chosen nothing from either column, in which case the "OPPTY Type Master" value should be ODE. Correct?

    In your OPPTY Type Master column, try the following:

    =IF(ISTEXT([Project Type]@row), [Project Type]@row, IF(ISTEXT([Program Type]@row), [Program Type]@row, "ODE"))

    In English, if there's a text value in Project Type, show that text here; otherwise, if there's a text value in Program Type, show that text here; otherwise (no text in either column,) show "ODE" here.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @John- Michael Diedrich

    So if I understand correctly, they select one of two values from "Project Type" column, OR they select one of two values from "Program Type" column, and you want whichever one thing they've chosen to be displayed in "OPPTY Type Master" column, unless they've chosen nothing from either column, in which case the "OPPTY Type Master" value should be ODE. Correct?

    In your OPPTY Type Master column, try the following:

    =IF(ISTEXT([Project Type]@row), [Project Type]@row, IF(ISTEXT([Program Type]@row), [Program Type]@row, "ODE"))

    In English, if there's a text value in Project Type, show that text here; otherwise, if there's a text value in Program Type, show that text here; otherwise (no text in either column,) show "ODE" here.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!