Combining multiple IF statements

Sabin800 · 09/14/18

Hi,

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!

Paul Newcome · 09/14/18

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

Sabin800 · 09/17/18

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?

Paul Newcome · 09/17/18

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(Completed@row = "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(Completed@row = "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.

KathyH · 09/19/18

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.

Paul Newcome · 09/19/18

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.

J. Craig Williams · 09/19/18

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.

Craig

liz.mayeux · 12/24/21

I find if you are creating if statements (or any multi criteria formula), I enter 1 formula at a time and make sure the response is correct then add the next layer to the formula. That way I know where to start making corrections if the dreaded INPARSABLE appears in the cell

Deesa Dontamsetti · 05/23/22

Hello @Paul Newcome ,

I need some help with combining is statements. I have one column which has two types, that is, Type is the column name and you can choose Type 1 or Type 2. IF it's Type 1, then I would need Primary Name column to return and if it's Type2, then I would need Secondary Name column to return. I was trying this statement:

IF(OR([Type]@row="Type1", [Type]@row="Type"2, Primary Name, Secondary Name)).

I am getting invalid operation. Thanks for your help in advance!!!

Genevieve P. · 05/24/22

Hi @Deesa Dontamsetti

In this instance you won't need an OR function. Instead, you'll want to list out your two instructions in order, first checking for "Type 1" then checking for "Type 2".

Try a structure like this:

=IF(Type@row= "Type1", [Primary Name]@row, IF(Type@row = "Type"2, [Secondary Name]@row))

Keep in mind that if "Type" is neither 1 or 2 this will return a blank cell. If this doesn't work, it would be helpful to see a screen capture, but please block out sensitive data.

Cheers!

Genevieve

sneenan · 05/24/22

Hi @Genevieve P.

I am also having some issues with a multiple if statement. I have a drop down selection of lead time and am trying to create a formula that will return the latest date an order can be expected to arrive. I have my purchase date column, lead time column with dropdown selections of 2 weeks at a time, and finally my lead time date column. I'm still fairly new and trying to get this all down but running into #UNPARSEABLE. My formula is as follows:

=IF([Lead Time]@row="1-2 Weeks", [Purchase Date]@row+14), IF([Lead Time]@row="2-3 Weeks", [Purchase Date]@row+21), if([Lead Time]@row="3-4 Weeks", [Purchase Date]@row+28), if([Lead Time]@row="4-5 Weeks", [Purchase Date]@row+35), if([Lead Time]@row="5-6 Weeks", [Purchase Date]@row+42), if([Lead Time]@row="6-7 Weeks", [Purchase Date]@row+49), if([Lead Time]@row="7-8 Weeks", [Purchase Date]@row+56), if([Lead Time]@row=">8 Weeks", [Purchase Date]@row+75))

Thanks in advance for any guidance!

Genevieve P. · 05/25/22

Hi @sneenan

You've done really well to get this far! The it's not working is that you're closing off each of your IF statements with a ) before then going into the next one.

Instead, you'll want to leave the ending open. This allows the formula to read the next IF should the first criteria not be met.

For example:

=IF([Lead Time]@row="1-2 Weeks", [Purchase Date]@row + 14, IF(...

This says:

If the Lead Time cell has "1-2 Weeks" selected, then return the Purchase Date + 14 days, OTHERWISE If...

Leaving the IF open allows the next iF statement to be the "otherwise" portion of the formula.Then at the very very end, once you've listed out all your options, you'll need to close off each and every IF. This means you'll have a lot of ))) in a row.

Note: If you don't know how many you need, leave it completely open and when you enter the formula into the cell Smartsheet will auto-fill the correct number for you.

Full formula example:

=IF([Lead Time]@row = "1-2 Weeks", [Purchase Date]@row + 14, IF([Lead Time]@row = "2-3 Weeks", [Purchase Date]@row + 21, IF([Lead Time]@row = "3-4 Weeks", [Purchase Date]@row + 28, IF([Lead Time]@row = "4-5 Weeks", [Purchase Date]@row + 35, IF([Lead Time]@row = "5-6 Weeks", [Purchase Date]@row + 42, IF([Lead Time]@row = "6-7 Weeks", [Purchase Date]@row + 49, IF([Lead Time]@row = "7-8 Weeks", [Purchase Date]@row + 56, IF([Lead Time]@row = ">8 Weeks", [Purchase Date]@row + 75))))))))

As a final note, you'll want to ensure this formula is being placed into a Date type of column, since you're asking it to return a date.

Cheers!

Genevieve

JennaD · 07/31/23

Hi @Genevieve P. - I am working on something similar and can't figure out how to allow for multiple conditions with the flexibility to know what to show, dependent on which is true. I've checked the parentheses and the order of my IF statements, but still not able to get the full formula to do ALL the things, only some of what I ask it for. This is my example: I'd like the "Progress %" column here to drive the "Status" color.

If the Progress % is 0 then I'd like the Status to be blank.

Here's what I came up with: =IF([Progress %]@row = 0, "").

I think this part is working.

If the Progress % is between 1 and 99 then I'd like the Status to be green.

Here's what I came up with: =IF(AND([Progress %]@row > 0, [Progress %]@row <= 99), "Green").

Struggling a little with this one bc even if I enter 100% in Progress %, it's still showing green, even though I think I'm instructing it to show me green ONLY between 0 and 99, but not at 100.

If the Progress % is 100 then I'd like the Status to be blue.

Here's what I came up with: =IF([Progress %]@row = "1", "Blue").

This works as a stand-alone formula.

I'm also trying to incorporate two other components:

If the Progress % is NOT 100 and the Due Date is past, I'd like the Status to be yellow.

Here's what I came up with: =IF([Due Date]@row < TODAY(), "Yellow").

This works as a stand-alone formula.

And finally, if the Risk flag is checked, I'd like the Status color to be red.

Here's what I came up with: =IF(Risk@row = true, "Red").

This works as a stand-alone formula.

Any insight on what I'm missing in order to put all of these components into one long formula so I use them in all the Status column cells to change the color depending on the criteria?

Thank you!

Genevieve P. · 08/01/23

Hey @JennaD

This was super easy to create for you because you had all the individual components! We just needed to put them in the right order.

A few things to call out:

Formulas use decimals to indicate percentage. This means 99 = 9900%, whereas 0.99 = 99%. This is why you were getting Green for 100%.
We'll want to start with the Red and Yellow instructions because they return regardless of percentage
Once those instructions are out of the way, then we can go into the Progress percent instructions. Keep in mind that since you already state that if the percent is 0 it should be blank, we don't need to add in that instruction for the Green statement.

Try this:

=IF(Risk@row = 1, "Red", IF([Due Date]@row < TODAY(), "Yellow", IF([Progress %]@row = 0, "", IF([Progress %]@row <= 0.99, "Green", IF([Progress %]@row = 1, "Blue")))))

Cheers,

Genevieve

JennaD · 08/01/23

@Genevieve P. - You are a life saver, thank you!! I knew it had to be possible, but was not getting the intended results. You were able to show me the correct order of operations, the correction on the percentage aspect and that if the risk flag is checked then it should be =1 and not =true. You also showed me that I did not need the AND statement to reiterate that green should be used when greater than 0 since we already included an instruction as to how to color the status when the Progress % is 0. This worked like a charm. Thank you so very much!!

Genevieve P. · 08/01/23

I'm so glad to hear it worked for you! Thanks for following up 🙂

Combining multiple IF statements

Comments

Categories