I'm new to smartsheets and trying to become more efficient at writing formulas. I've written four if statements that perform certain tasks based on the criteria. I'd like it to all be one statement that I can put into one of my cells, however I can't quite seem to figure it out. I've read other users posts and some of the Smartsheets documents but I'm still unable to get it working. Here are my four statements:


### If the end date is less that today's date, and the column 'Completed' is full,

### then change the RYG Ball to 'Green'. Otherwise, turn it 'red'.

=IF(AND([End Date]3 < TODAY(), [Completed]3 = "Full"), "Green", "Red")

### if End Date is less than today and Completed is not full then make it red

=IF(AND([End Date]3 < TODAY(), [Completed]3 = "Empty"), "Red", "Green")

### if End Date is greater than today make it green

=IF(AND([End Date]3 > TODAY()), "Green", "Red")

### if End Date is 2 days before today and Completed is not full then make it yellow

=IF(AND([End Date]3 < TODAY() - 2, [Completed]3 = "Empty"), "Yellow", "Green")


Any help would be greatly appreciated!


An IF statement is built in the order of =IF(Logical Statement is true, then enter this, otherwise enter this)


To nest them, you would put the next IF statement in the otherwise part of the first and close them both out at the same time at the end of the second one.


=IF(Statement 1, "Answer One", IF(Statement 1, Answer Two"))

Thanks for the reply, I've continued to test multiple statements to get it working. So far, I keep getting "Unparseable" when I use this statement:

=IF(AND([End Date]3 < TODAY(), [Completed]3 = "Full", "Green", IF(AND([End Date]3 < TODAY(), [Completed]3 = "Empty", "Red"), IF(AND([End Date]3 > TODAY(), "Green", IF(AND([End Date]3 < TODAY() - 2, [Completed]3 = "Empty", "Yellow", "Green") "Red") "Green") "Red"))))

Am I nesting the "IF" statements correctly?

Close. In your first two, you have the correct layout, but you forgot to close your AND statements.


Your third IF, you have an AND, but did not include an additional criteria. You will also need to close that AND.


Your fourth IF, you didn't close the AND statement.


Finally, the multiple "otherwise" (the third part of an IF statement) portions is incorrect. Your first "otherwise is the second IF. Your second otherwise is your third IF. So on and so forth.


Here is a formula that does EXACTLY as you have in your first post.


=IF(OR([email protected] = "Full", [End Date]@row > TODAY()), "Green", IF([End Date]@row < TODAY(-2), "Yellow", "Red"))


Although I feel like what you are trying to achieve is more along the lines of 


=IF(OR([email protected] = "Full", [End Date]@row > TODAY(2)), "Green", IF([End Date]@row > TODAY(), "Yellow", "Red"))


Give them both a try with different dates and let me know if either of them get you the results you're looking for.

I find Smartsheet formulas tend to be much longer than Excel because of the way Smartsheet constructs them. The longer the formula, the harder it is to troubleshoot.

Sometimes I find it easier to do 1 IF statement per column, and then have a final column to roll up results. I hide my interim columns.

In reply to by KathyH

I'll do that or build a table and use INDEX/MATCH if it gets to be too long, but this particular case can be resolved using only 2 nested IF statements. Something shorter like this, I usually just plug in.

Smartsheet uses column names and don't have R1C1 notation. They will always be longer.

If I might need the intermediate value, I'll use multiple columns too.