Multiple IF Statements

Ken20791

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")

Genevieve P.

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.

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.

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

Genevieve P.

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

Ken20791

Works perfectly Genevieve. Thank You!

Ken20791

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.

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

Ken20791

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.

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.

Ken20791

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

Genevieve P.

No problem! 🙂

Ken20791

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.

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

Ken20791

That worked - Thanks Again!