Another Nested IF Formula

11/29/21
Accepted

Could use your help again. Not sure why I have so much trouble with these nested IF statements but I do! When I use the formula below and the Business Unit is equal to EMEA, it returns ASD....any ideas?

=IF([Business Unit]@row = "Biologics", "ASD", IF([Business Unit]@row = "Western", "ASD", IF([Business Unit]@row = "Simple Plex", "ASD", IF([Business Unit]@row <> "Immunoassay", "ASD", IF([Business Unit]@row = "Antibodies", "RSD", IF([Business Unit]@row = "Proteins", "RSD", IF([Business Unit]@row = "Cell & Gene Therapy", "RSD", IF([Business Unit]@row = "Small Molecules", "RSD", IF([Business Unit]@row = "Asuragen", "MDD", IF([Business Unit]@row = "EMEA", "EMEA"))))))))))

Tags:

Best Answer

  • David TutwilerDavid Tutwiler Overachievers
    Accepted Answer

    <> does not mean contains it means Not Equal To. If you want to use contains you'll need to use the wrapper:

    IF(CONTAINS("Immunoassay", [Business Unit]@row), "ASD"

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Are you able to provide a screenshot of the formula actually in the sheet as well as a screenshot of the row where it is outputting the wrong text?

    thinkspi.com

  • David TutwilerDavid Tutwiler Overachievers

    I think you have a typo at this part of the formula:

    IF([Business Unit]@row <> "Immunoassay"

    I think this should be IF([Business Unit]@row = "Immunoassay"

    What's happening is it gets to that part of the formula and says, "@row is not equal to Immunoassay, therefore I need to put down ASD".

    Also, you could simplify your calls with an OR. Something like:

    =IF(OR([Business Unit]@row = "Biologics", [Business Unit]@row = "Western", [Business Unit]@row = "Simple Plex", [Business Unit]@row = "Immunoassay"), "ASD"

  • BTMarketingOpsBTMarketingOps ✭✭✭✭✭

    Attached is a screen shot of the row that is giving me the incorrect result. The reason I have <> in front of Immunoassay is because it is actually Immunoassay (ELISA/Luminex) but the formula gets confused with the () in that so I used the carrots to mean contains Immunoassay. Hope that makes sense.

    Here is the for

    mula that is in that row:

    =IF([Business Unit]@row = "Biologics", "ASD", IF([Business Unit]@row = "Western", "ASD", IF([Business Unit]@row = "Simple Plex", "ASD", IF([Business Unit]@row <> "Immunoassay", "ASD", IF([Business Unit]@row = "Antibodies", "RSD", IF([Business Unit]@row = "Proteins", "RSD", IF([Business Unit]@row = "Cell & Gene Therapy", "RSD", IF([Business Unit]@row = "Small Molecules", "RSD", IF([Business Unit]@row = "Asuragen", "MDD", IF([Business Unit]@row = "EMEA", "EMEA"))))))))))

  • David TutwilerDavid Tutwiler Overachievers
    Accepted Answer

    <> does not mean contains it means Not Equal To. If you want to use contains you'll need to use the wrapper:

    IF(CONTAINS("Immunoassay", [Business Unit]@row), "ASD"

  • BTMarketingOpsBTMarketingOps ✭✭✭✭✭

    @Paul Newcome Did I post what you were looking for?

  • BTMarketingOpsBTMarketingOps ✭✭✭✭✭

    @Paul Newcome @David Tutwiler Never mind, David's last comment did solve my issue! Thanks David!

  • David TutwilerDavid Tutwiler Overachievers

    No problem. Glad it's working for you. Do you mind marking that as the answer for anyone who might come along this post in the future? It will move that up towards the top so they can see it.

  • BTMarketingOpsBTMarketingOps ✭✭✭✭✭

    @David Tutwiler I accidentally clicked on No for Did it answer the question. Is there any way for you to undo that?

  • David TutwilerDavid Tutwiler Overachievers

    I can't but I'll try and tag some folks from Smartsheet who might be able to. No worries and glad you got your formula working.

    @Maxwell Griffith @Genevieve P.

  • Hiya! Adjusted! Thanks for keeping the Community up-to-date.

    Cheers,

    Genevieve

  • David TutwilerDavid Tutwiler Overachievers

    Thank you Genevieve

Sign In or Register to comment.