Multiple IF Statements

How would I string these IF statements into one cell?

IF(OR([Project_Group]@row = "ASOS", [Project_Group]@row = "NEXRAD", [Project_Group]@row = "Grandview", [Project_Group]@row = "NWR", [Project_Group]@row = "VOIP Sites", [Project_Group]@row = "OneNWSNet/NCEP"), "NWS")

IF(OR([Project_Group]@row = "AAMB", [Project_Group]@row = "COOPS", [Project_Group]@row = "IOOS", [Project_Group]@row = "NCCOS", [Project_Group]@row = "NGS", [Project_Group]@row = "OCM", [Project_Group]@row = "ONMS", [Project_Group]@row = "ORR"), "NOS")

IF([Project_Group]@row = "NESDIS", "NESDIS")

IF([Project_Group]@row = "NMFS", "NMFS")

IF([Project_Group]@row = "OAR", "OAR")

IF([Project_Group]@row = "OMAO", "OMAO")

IF([Project_Group]@row = "Staff & Executives", "Staff & Executives")

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Ken20791

    You've done all the hard work of creating the statements. All you need to do is remove each ) at the end and replace it with a comma, letting the last bit of the formula close off ALL of the parentheses, like so:


    IF(OR([Project_Group]@row = "ASOS", [Project_Group]@row = "NEXRAD", [Project_Group]@row = "Grandview", [Project_Group]@row = "NWR", [Project_Group]@row = "VOIP Sites", [Project_Group]@row = "OneNWSNet/NCEP"), "NWS", IF(OR([Project_Group]@row = "AAMB", [Project_Group]@row = "COOPS", [Project_Group]@row = "IOOS", [Project_Group]@row = "NCCOS", [Project_Group]@row = "NGS", [Project_Group]@row = "OCM", [Project_Group]@row = "ONMS", [Project_Group]@row = "ORR"), "NOS", IF([Project_Group]@row = "NESDIS", "NESDIS", IF([Project_Group]@row = "NMFS", "NMFS", IF([Project_Group]@row = "OAR", "OAR", IF([Project_Group]@row = "OMAO", "OMAO", IF([Project_Group]@row = "Staff & Executives", "Staff & Executives")))))))


    However, I would actually shorten this. Your last 5 statements all want the cell to repeat exactly what it says. Instead of saying:

    IF([Project_Group]@row = "NESDIS", "NESDIS")

    You could just have it return [Project_Group]@row.

    This means you can have your first to IF(OR()) statements, then if neither of those statements are true, return [Project_Group]@row:

    IF(OR([Project_Group]@row = "ASOS", [Project_Group]@row = "NEXRAD", [Project_Group]@row = "Grandview", [Project_Group]@row = "NWR", [Project_Group]@row = "VOIP Sites", [Project_Group]@row = "OneNWSNet/NCEP"), "NWS", IF(OR([Project_Group]@row = "AAMB", [Project_Group]@row = "COOPS", [Project_Group]@row = "IOOS", [Project_Group]@row = "NCCOS", [Project_Group]@row = "NGS", [Project_Group]@row = "OCM", [Project_Group]@row = "ONMS", [Project_Group]@row = "ORR"), "NOS", [Project_Group]@row))


    Cheers,

    Genevieve

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Ken20791

    Have you Saved the sheet since adding data in the rows? The Auto Number column will populate data as soon as the sheet/rows have been saved. This should then update your formulas.

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Ken20791

    Yes!

    Add an IF statement in the beginning:

    =IF([Project_Group]@row = "", "", [Project_Group]@row + "-" + RANKEQ([Row#]@row, COLLECT([Row#]:[Row#], [Project_Group]:[Project_Group], [Project_Group]@row), 1))


    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Ken20791

    You've done all the hard work of creating the statements. All you need to do is remove each ) at the end and replace it with a comma, letting the last bit of the formula close off ALL of the parentheses, like so:


    IF(OR([Project_Group]@row = "ASOS", [Project_Group]@row = "NEXRAD", [Project_Group]@row = "Grandview", [Project_Group]@row = "NWR", [Project_Group]@row = "VOIP Sites", [Project_Group]@row = "OneNWSNet/NCEP"), "NWS", IF(OR([Project_Group]@row = "AAMB", [Project_Group]@row = "COOPS", [Project_Group]@row = "IOOS", [Project_Group]@row = "NCCOS", [Project_Group]@row = "NGS", [Project_Group]@row = "OCM", [Project_Group]@row = "ONMS", [Project_Group]@row = "ORR"), "NOS", IF([Project_Group]@row = "NESDIS", "NESDIS", IF([Project_Group]@row = "NMFS", "NMFS", IF([Project_Group]@row = "OAR", "OAR", IF([Project_Group]@row = "OMAO", "OMAO", IF([Project_Group]@row = "Staff & Executives", "Staff & Executives")))))))


    However, I would actually shorten this. Your last 5 statements all want the cell to repeat exactly what it says. Instead of saying:

    IF([Project_Group]@row = "NESDIS", "NESDIS")

    You could just have it return [Project_Group]@row.

    This means you can have your first to IF(OR()) statements, then if neither of those statements are true, return [Project_Group]@row:

    IF(OR([Project_Group]@row = "ASOS", [Project_Group]@row = "NEXRAD", [Project_Group]@row = "Grandview", [Project_Group]@row = "NWR", [Project_Group]@row = "VOIP Sites", [Project_Group]@row = "OneNWSNet/NCEP"), "NWS", IF(OR([Project_Group]@row = "AAMB", [Project_Group]@row = "COOPS", [Project_Group]@row = "IOOS", [Project_Group]@row = "NCCOS", [Project_Group]@row = "NGS", [Project_Group]@row = "OCM", [Project_Group]@row = "ONMS", [Project_Group]@row = "ORR"), "NOS", [Project_Group]@row))


    Cheers,

    Genevieve

  • Works perfectly Genevieve. Thank You!

  • I'm working on another column where I need the Record ID(first column) to be a combination of the group name from group name column and the number of times the group name shows up in the group name column. Example - if the group name = "dog" and appears in the group name column once - the Record ID would = dog-1. The record ID must change as the number of times "dog" appears in the group name column increases - dog-2, dog-3, etc. My formula is not doing this. It's changing all Record IDs to the number of times "dog" appears in the group name column instead of making the Record IDs distinct. I made the Record ID column = the formula below. How can I change it to produce distinct Record IDs?

    [Project_Group]@row + "-" + (COUNTIF([Project_Group]:[Project_Group], [Project_Group]@row))

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Ken20791

    To do this, we'll want to add two helper columns into your sheet.

    • The First Column is an Auto-Number column (titled "Row ID" in my sheet)
    • The Second Column uses the MATCH function to always have the Row Number displayed in a cell (titled "Row Number")

    =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

    I would suggest making this a Column Formula. You can hide these columns in your sheet.


    The reason we do this is so that we have the Row Number displayed even after shifting or moving rows around. Watch what happens when I move row 3 to row 2... the Row ID stays the same (the Auto Number), but my MATCH formula updates the Row Number cell:


    Now that we have the Row Number displayed, we can use a RANKEQ function to rank the row number based on what's in the Project Group column, like so:

    =[Project_Group]@row + " - " + RANKEQ([Row Number]@row, COLLECT([Row Number]:[Row Number], [Project_Group]:[Project_Group], [Project_Group]@row), 1)

    See: RANKEQ Function and COLLECT Function

    Cheers,

    Genevieve

  • I have made the changes but it's not quite working as expected. The Row ID column is not showing any numbers and appears that it makes each Row ID = 1 because each formula ends with a "1" - dog1, cat1 even though I have more than 1 of each in the Project_Group column.


  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Ken20791

    Have you Saved the sheet since adding data in the rows? The Auto Number column will populate data as soon as the sheet/rows have been saved. This should then update your formulas.

  • Working like a charm now after saving the sheet. Thanks Again Genevieve!

  • Genevieve P.
    Genevieve P. Employee Admin

    No problem! 🙂

  • Genevieve,

    [Project_Group]@row + "-" + RANKEQ([Row#]@row, COLLECT([Row#]:[Row#], [Project_Group]:[Project_Group], [Project_Group]@row), 1)

    The above formula results in a Record_ID of -1, -2, -3, etc. when no Project_Group is selected as shown below. Any way to make the column blank until a Project_Group is selected?


  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Ken20791

    Yes!

    Add an IF statement in the beginning:

    =IF([Project_Group]@row = "", "", [Project_Group]@row + "-" + RANKEQ([Row#]@row, COLLECT([Row#]:[Row#], [Project_Group]:[Project_Group], [Project_Group]@row), 1))


    Cheers,

    Genevieve

  • That worked - Thanks Again!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!