Nested IF CONTAINS, Return Blank When Contains Blank

Options

I've searched the Community and haven't found this situation, so I appreciate any help. I have a data set in which I'm trying to write a nested IF CONTAINS formula to return the primary program based on what is contained in the Project ID field.

My current formula is =IF(CONTAINS("ABC", [Project ID]:[Project ID}), "ABC", IF(CONTAINS("XYZ", [Project ID]:[Project ID]), "XYZ", IF(CONTAINS("KLM", [Project ID]:[Project ID]), "KLM", "")))

ABC is returned for every row because that is listed first in the formula.


The second formula I'm working on is to return the Active Program the person is working based on Program Shared To, Program Shared From, Shared Start Date, Shared End Date, and Term Date.

--When Program Shared To is XYZ and Program Shared From is ABC with no Shared End Date and no Term Date, the Active Program should return XYZ.

--When Program Shared To is XYZ and Program Shared From is ABC with a Shared End Date and no Term Date, the Active Program should return ABC.

--When a Term Date is included, Primary Program and Active Program should return blanks.

Thanks for your help!

Lori

Tags:

Best Answer

  • Monique Odom-Stearn
    Monique Odom-Stearn ✭✭✭✭✭✭
    Answer ✓
    Options

    Hello @Lori Flanigan,

    Your first formula is searching the entire column for your criteria, instead of just the field in that row, so it's always returning ABC. Try changing it to the following, so it returns the Primary Program listed in that row and is blank if the term date is not blank:

    =IF(NOT(ISBLANK([Term Date]@row)),"", IF(CONTAINS("ABC", [Project ID]@row), "ABC", IF(CONTAINS("XYZ", [Project ID]@row), "XYZ", IF(CONTAINS("KLM", [Project ID]@row), "KLM", ""))))

    For your second request, try the following:

    =IF(NOT(ISBLANK([Term Date]@row)), "", IF(ISBLANK([Shared End Date]@row), [Program Shared To]@row, [Program Shared From]@row))

    If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!

    Monique Odom-Stearn

    Business Process Excellence Manager

    Smartsheet Leader & Community Champion

    Pronouns: She/Her (What’s this?)

    “Take chances, make mistakes, get messy!” – Ms. Frizzle

Answers

  • Monique Odom-Stearn
    Monique Odom-Stearn ✭✭✭✭✭✭
    Answer ✓
    Options

    Hello @Lori Flanigan,

    Your first formula is searching the entire column for your criteria, instead of just the field in that row, so it's always returning ABC. Try changing it to the following, so it returns the Primary Program listed in that row and is blank if the term date is not blank:

    =IF(NOT(ISBLANK([Term Date]@row)),"", IF(CONTAINS("ABC", [Project ID]@row), "ABC", IF(CONTAINS("XYZ", [Project ID]@row), "XYZ", IF(CONTAINS("KLM", [Project ID]@row), "KLM", ""))))

    For your second request, try the following:

    =IF(NOT(ISBLANK([Term Date]@row)), "", IF(ISBLANK([Shared End Date]@row), [Program Shared To]@row, [Program Shared From]@row))

    If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!

    Monique Odom-Stearn

    Business Process Excellence Manager

    Smartsheet Leader & Community Champion

    Pronouns: She/Her (What’s this?)

    “Take chances, make mistakes, get messy!” – Ms. Frizzle

  • Lori Flanigan
    Lori Flanigan ✭✭✭✭✭
    Options

    Monique, thank you so much for your quick and perfect formulas! I get confused about when the column must be used in the formula vs. using @row.

    I appreciate your help!

    Lori

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!