Multiple IF Statements

kadam56146

Can we do multiple IF statements? I've tried to use IF and also OR to complete the function I'm trying to do and nothing seems to work. I want my Status column, "Not Started", "In Progress", and "Completed" to be based off of the % indicated in the % Completed column so I don't have to remember to change the status when the % completed changes. I know I've done this before in Excel but not sure Smartsheet can handle an IF statement with multiple conditions.

Here is the logic...

IF % Completed = 0%, then return "Not Started"

IF % Completed = 100%, then return "Completed"

IF % Completed is between 1 - 99, then return "In Progress" OR this could also be IF the first two logics are FALSE then return "In Progress".

 

Thank you,

Kim

Lee Joramo

You chain together additional conditions within the IF function.

=IF([% Complete]2 = 0, "Not Started", IF([% Complete]2 = 1, "Complete", "In Progress"))

In this case, I have placed a second IF function in the False result of the first IF function. NOTE: you can put IF's or other functions in an IF functions TRUE or FALSE returns.

Lee Joramo

You chain together additional conditions within the IF function.

=IF([% Complete]2 = 0, "Not Started", IF([% Complete]2 = 1, "Complete", "In Progress"))

In this case, I have placed a second IF function in the False result of the first IF function. NOTE: you can put IF's or other functions in an IF functions TRUE or FALSE returns.

Mike Wilday

It's important to note a couple things. Percentages, though displayed with a % sign, are actually decimals in the backend. 1 = 100%, .5 = 50%, etc. etc. 

So if you wanted to add an additional status based on a lesser percentage, then you would use the decimal. .5, .6, etc. 

kadam56146

Thank you this was very helpful, it was the second "IF" that I was missing.

Elizabeth Jones

I'm getting an unparseable error and can't figure out what the issue is. Here is my formula:

=

IF([Q3 2020 Velocity Trend]1 > [Working Velocity]1, "Up”,

IF([Q3 2020 Velocity Trend]1 < [Working Velocity]1, "Down”, 

IF([Q3 2020 Velocity Trend]1 = [Working Velocity]1, "Unchanged”, “Fix Me”)))

The test lines all worked and I placed it into a text editor and made the suggestions from the video, but it just says unparseable?

Elizabeth Jones

@Mike Wilday any idea what I could be doing incorrectly? ^

Andrée Starå

Hi @Elizabeth Jones

You have characters that are wrong. See below.

” “

=

IF([Q3 2020 Velocity Trend]1 > [Working Velocity]1, "Up”,

IF([Q3 2020 Velocity Trend]1 < [Working Velocity]1, "Down”, 

IF([Q3 2020 Velocity Trend]1 = [Working Velocity]1, "Unchanged”, “Fix Me”)))

Here's the fixed one.

=

IF([Q3 2020 Velocity Trend]1 > [Working Velocity]1, "Up",

IF([Q3 2020 Velocity Trend]1 < [Working Velocity]1, "Down", 

IF([Q3 2020 Velocity Trend]1 = [Working Velocity]1, "Unchanged", "Fix Me")))

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Elizabeth Jones

It did work! It appears the " from the text editor is causing the issue. Thanks for helping me solve this :)

Andrée Starå

@Elizabeth Jones

You're more than welcome!

Yes, some text editors adds strange characters.

Mike Wilday

@Elizabeth Jones Yeah, if you use a word processor to develop your formulas, they frequently add "Smart Quotes" in place of standard quotations. It's always best practice to construct your formulas in wordpad, notepad ++ or a similar simple text-editor that strips out all formatting.

Hardik Ranpara

Can someone please tell me how to use multipe IF function using more than 1 Smartsheet? Thank you in advance.

Sharad Jain

I am trying to use multiple IF statements and it is not working. For example, I have a column called "HCSA Goal Alignment" which has three conditions that a user can choose. The three conditions are:

·       Does not meet (Low priority = 5 points)

·       Partially meets (Medium priority = 10 points)

·       Completely meets (High priority = 15 points)

 

I have created another column called "Score" where I am trying to create a formula with multiple IF statements to calculate the result (5, 10, or 15) based on the above three inputs. Here is my formula:

 

·       =IF([HCSA Goal Alignment]@row = "Does not meet (Low priority =5 points)", "5", IF([HCSA Goal Alignment]@row = "Partially meets (Medium priority =10 points)", "10", IF([HCSA Goal Alignment]@row = "Completely meets = (High priority = 15points)", "15")))

Genevieve P.

Hi @Sharad Jain

The formula is structured correctly! However your quotes around the desired output (ex. "5") turn the numbers into text values.

Try tis:

=IF([HCSA Goal Alignment]@row = "Does not meet (Low priority =5 points)", 5, IF([HCSA Goal Alignment]@row = "Partially meets (Medium priority =10 points)", 10, IF([HCSA Goal Alignment]@row = "Completely meets = (High priority = 15points)", 15)))

Keep in mind the values it's looking for in your "HCSA Goal Alignment" column will need to be spelled exactly the same in your formula.

If this doesn't work, can you post a screen capture of the values in the drop-down list, and the error or incorrect result you're seeing?

Cheers,

Genevieve

william.johnson

Hi, I am trying to nest two IF statements that reference other sheets. Each statement alone works:

=IF([New Time Product]@row = "Dimensions", INDEX({WFD/Ulti Master Roll Up Range 2}, MATCH([OpenAir Project]@row, {WFD/Ulti Master Roll Up Range 3}, 0)))

=IF([New Time Product]@row = "Ready", INDEX({WFR Master Roll Up Range 2}, MATCH([Solution ID]@row, {WFR Master Roll Up Range 1}, 0)))

But if I try to nest them like this I get an incorrect argument set error. What am I doing wrong?

=IF([New Time Product]@row = "Dimensions", INDEX({WFD/Ulti Master Roll Up Range 2}, MATCH([OpenAir Project]@row, {WFD/Ulti Master Roll Up Range 3}, 0, =IF([New Time Product]@row = "Ready", INDEX({WFR Master Roll Up Range 2}, MATCH([Solution ID]@row, {WFR Master Roll Up Range 1}, 0)))))

william.johnson

Removed the extra = and I get the same error

=IF([New Time Product]@row = "Dimensions", INDEX({WFD/Ulti Master Roll Up Range 2}, MATCH([OpenAir Project]@row, {WFD/Ulti Master Roll Up Range 3}, 0, IF([New Time Product]@row = "Ready", INDEX({WFR Master Roll Up Range 2}, MATCH([Solution ID]@row, {WFR Master Roll Up Range 1}, 0)))))

Kelly Moore

Hey @william.johnson

It looks like a few parentheses were out of place.

=IF([New Time Product]@row = "Dimensions", INDEX({WFD/Ulti Master Roll Up Range 2}, MATCH([OpenAir Project]@row, {WFD/Ulti Master Roll Up Range 3}, 0)), IF([New Time Product]@row = "Ready", INDEX({WFR Master Roll Up Range 2}, MATCH([Solution ID]@row, {WFR Master Roll Up Range 1}, 0))))

Does this work for you?