# Multiple IF Statements

Options
edited 07/15/24

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

Tags:

• ✭✭✭✭✭✭
edited 07/30/18 Answer ✓
Options

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.

• ✭✭✭✭✭✭
edited 07/30/18 Answer ✓
Options

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.

• ✭✭✭✭✭✭
Options

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.

• Options

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

• Options

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?

• Options

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

• ✭✭✭✭✭✭
Options

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

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

• ✭✭✭✭✭✭
Options

You're more than welcome!

Yes, some text editors adds strange characters.

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

@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.

• Options

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

• Options

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

• Employee
Options

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

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!