If Contains Formula

Options

Hi there,

I need help with putting together these 2 formula's:

If "How does the Company make money or plan to do so?" selection does NOT include “Other” then show below:

 [Name of Company] makes or plans to make money through [Other]

If "How does the Company make money or plan to do so?" selection does include “Other” then show below:

 [Name of Company] makes or plans to make money through: [other] and through [Other].

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    You don't need the second IF at all. The 3rd portion of the IF statement is where you put your output for when the first argument is not true.


    You have

    =IF(CONTAINS(.....), ....., IF(NOT(CONTAINS(.....)), .....))


    You only need

    =IF(CONTAINS(.....), ....., .....)


    It also looks like your syntax for the CONTAINS function is backwards.

    You have

    CONTAINS([search here]@row, "search for this")


    You should have

    CONTAINS("search for this", [search here]@row)

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you able to provide some screenshots for context?

  • Brittanyy
    Brittanyy ✭✭✭✭
    Options

    @Paul Newcome

    Sure, here is a screenshot for context:


    If "How does the Company make money or plan to do so?" selection does NOT include “Other” then show below:

    Context: if Other(please explain) is multiselected in the dropdown


    Formula's I want to do:

    If "How does the Company make money or plan to do so?" selection does NOT include “Other(Please Explain)” then show below:

     [Name of Company] makes or plans to make money through ["How does the Company make money or plan to do so?"]

    If "How does the Company make money or plan to do so?" selection does include “Other” then show below:

     [Name of Company] makes or plans to make money through: [How does the Company make money or plan to do so?] and through [2a.Other].

    Tags: Formulas

  • Brittanyy
    Brittanyy ✭✭✭✭
    Options

    @Paul Newcome

    I have this formula:

    =IF(CONTAINS([How does the Company make money or plan to do so?]@row, "Other (please explain)"), [Name of Company]@row + " makes or plans to make money through: " + [How does the Company make money or plan to do so?]@row + " and through " + [2a. Other]@row, [Name of Company]@row + " makes or plans to make money through: " + [How does the Company make money or plan to do so?]@row + ".")

    But, It only answers part of the equation. I can't seem to figure out the does not contain.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @Brittanyy you would just need to use the 3rd portion (value if false) of the IF statement.


    You are currently using

    =IF(this is true, do this)


    Just fill in the 3rd portion of the IF statement like so:

    =IF(this is true, do this, otherwise do this)

  • Brittanyy
    Brittanyy ✭✭✭✭
    Options

    @Paul Newcome

    I edited my formula below, but I am still getting an error.

    =IF(CONTAINS([How does the Company make money or plan to do so?]@row, "Other (please explain)"), [Name of Company]@row + " makes or plans to make money through: " + [How does the Company make money or plan to do so?]@row + " and through " + [2a. Other]@row. IF(NOT(CONTAINS([How does the Company make money or plan to do so?]@row, "Other (please explain)")+([Name of Company]@row + " makes or plans to make money through: " + [How does the Company make money or plan to do so?]@row + ".")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    You don't need the second IF at all. The 3rd portion of the IF statement is where you put your output for when the first argument is not true.


    You have

    =IF(CONTAINS(.....), ....., IF(NOT(CONTAINS(.....)), .....))


    You only need

    =IF(CONTAINS(.....), ....., .....)


    It also looks like your syntax for the CONTAINS function is backwards.

    You have

    CONTAINS([search here]@row, "search for this")


    You should have

    CONTAINS("search for this", [search here]@row)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!