Need Help with multiple IF statements within a formula
Attempting to update a status column to show either Not Started, In Progress, or Complete based on blank or not blank statements from other columns.
These formulas work by them selves.
=IF(COLUMN1@row = "", "Not Started", "In Progress")
=IF([COLUMN 10]@row <> "", "Complete")
but put together and they don't. I get the UNPARSEABLE error message.
Best Answers

Hi @MannyDLC ,
If you want to have multiple If statements, you'll need to nest them inside one another.
For these two statements I would suggest the following:
=IF([COLUMN1]@row = "", "Not Started", IF([COLUMN 10]@row <> "", "Complete", "In Progress"))
I would also suggest that you use values in the expression portion of the if statement. Perhaps a percentage.
=IF([COLUMN1]@row = 0, "Not Started", IF([COLUMN1]@row = 100, "Complete", "In Progress"))
This formula is basically saying,
If column one is equal to zero, then show "Not Started", otherwise if it is equal to 100, show "Complete", otherwise if non of those conditions are met, then show "In Progress".
Hope this helps!

The way to nest IF statements really varies with the data involved. Then it boils down to the logic underneath it all.
In this case, I would start with [COLUMN 10]. The logic says "if there's a value in COLUMN 10, set the status to "Complete". Easy enough. But what happens if there's no value in Column 10? In that case, you want to take a look at what's in COLUMN1, yes? So the IF statement you have for COLUMN1 becomes nested as the negative condition in the logic for COLUMN10:
=IF([COLUMN 10]@row <> "", "Complete", IF(COLUMN1@row = "", "Not Started", "In Progress"))
English: If there's a value in COLUMN 10, set the status to "Complete", otherwise, check the value in COLUMN1; if COLUMN1 is blank, set the status to "Not Started", otherwise, set the status to "In Progress".
 Make sure your first and last parentheses are the same color. The second "IF" is selfcontained with the first "IF", so you have to close off the second IF with a ")" and then close off the first IF with another ")". Rule of thumb is the number of end parentheses at the end equals the number of IFs.
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers

Hi @MannyDLC ,
If you want to have multiple If statements, you'll need to nest them inside one another.
For these two statements I would suggest the following:
=IF([COLUMN1]@row = "", "Not Started", IF([COLUMN 10]@row <> "", "Complete", "In Progress"))
I would also suggest that you use values in the expression portion of the if statement. Perhaps a percentage.
=IF([COLUMN1]@row = 0, "Not Started", IF([COLUMN1]@row = 100, "Complete", "In Progress"))
This formula is basically saying,
If column one is equal to zero, then show "Not Started", otherwise if it is equal to 100, show "Complete", otherwise if non of those conditions are met, then show "In Progress".
Hope this helps!

The way to nest IF statements really varies with the data involved. Then it boils down to the logic underneath it all.
In this case, I would start with [COLUMN 10]. The logic says "if there's a value in COLUMN 10, set the status to "Complete". Easy enough. But what happens if there's no value in Column 10? In that case, you want to take a look at what's in COLUMN1, yes? So the IF statement you have for COLUMN1 becomes nested as the negative condition in the logic for COLUMN10:
=IF([COLUMN 10]@row <> "", "Complete", IF(COLUMN1@row = "", "Not Started", "In Progress"))
English: If there's a value in COLUMN 10, set the status to "Complete", otherwise, check the value in COLUMN1; if COLUMN1 is blank, set the status to "Not Started", otherwise, set the status to "In Progress".
 Make sure your first and last parentheses are the same color. The second "IF" is selfcontained with the first "IF", so you have to close off the second IF with a ")" and then close off the first IF with another ")". Rule of thumb is the number of end parentheses at the end equals the number of IFs.
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Please provide some more information about the 2 columns you are referencing.
Is there a value you check to determine if the task is complete?
For example:
If column X is 20%, then the status column is "in progress." or if column x < 100%, then the status is "in progress."
I think we can get you the correct formula if you provide some additional information.
Frank Smith, PMP
Assistant Director  IT Special Projects Mgr.
Oregon Parks & Recreation Department
If my response helps, please mark it as an accepted answer. 😎
Help Article Resources
Categories
Check out the Formula Handbook template!