Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Combining IF/IF AND Statements
Hello!
I cannot seem to figure out how to put together two working IF and IF(AND...) statements.
Thank you for any direction on how to accomplish.
Sandy
If I put "IF(AND..." in front of all the IF statements, only my first two statements work. The rest are ignored.
If I remove the "(AND..." from only the first two statements, I get unparseable or incorrect argument set (depending on where I place my parens).
=IF([Project or Task Status]1 = "Rolled over to another project", [Last Updated]1 = ""), "", IF([Project or Task Status]1 = "Removed", [Last Updated]1 = ""), "", IF(AND([Project or Task Status]1 = "Removed"), " ", IF(AND([Last Updated]1 = " "), "Red", IF(AND(Done1 = 1), "Blue", IF(AND([Last Updated]1 >= TODAY() - 7, Done1 = 0), "Green", IF(AND([Last Updated]1 >= TODAY() - 15, Done1 = 0), "Yellow", "Red")))))
Comments
-
To combine IF and AND statements you have to close the AND statement before you put the THEN statement. And you have to put each AND logical expression together, separated by commas, right after the And.
See this on an explanation of the and statement: https://help.smartsheet.com/function/and
IF(AND([Last Updated]1="", [Project or Task Status]1="removed"), "And Statement Completed", IF(AND(Done1=1, [Last Updated]1 >=Today()-7), "Blue","Green"))
In my example, I am nesting two IF statements with ANDs. The And function requires you to list your logical expressions one after another with a comma separating. I.E. IF(AND(Column1=1, Column2 = 2), "Three", IF(And( Column1=2, Column2 = 3, Column3=0), "Five","Zero"))
Basically it is looking to see if BOTH column 1 =1 and column 2 =2. If so, then it returns Three. If not, then it checks to see if Column1 =2, column2 = 3, and Column3 = 0 and IF SO then it returns Five, if not, then it returns Zero.
I would restructure your formula to make sure your AND statement includes every logical expression separated by commas, then, whatever you want to return based on all of those items being true.
-
Mike:
Thank you for your reply. I am sorry for my delayed response. I am sure I received a notice from SS of it.
I will study and try as you indicated. I really appreciate your input.
Sandy
-
IF you run into any issues, feel free to post the formulas here and I and other community members will attempt to help you get them working!
