How to create IF formula with multiple conditions
Hi everyone,
Newer Smartsheet user here! 😊
I am setting up sheets to capture ongoing project status updates from team members.
I have a (dropdown)column that shows QI Phase and a blank column that shows % complete.
Ask: I am trying to figure out how to write a formula that will reflect a % complete based on what phase a project is in.
If the project is in the "Initiate Project" or "Assess the Current State" then I want the percent complete to show less than 25%.
If the project is in the "Design the Future State" or "Test and Implement" phase then I want the percent complete to show 2550%.
If the project is in the "Sustain" or "Spread" phase then I want the percent complete to show 50 to 75%.
If the project is in the "Completed: Transferred to Operations" phase then I want it to show 100% complete.
Any guidance would be super appreciated!!!
Sade
Best Answer

Hi @SadeMJB
Start with an IF OR function like this:
=IF(OR([QI Phase]@row = "Initiate Project", [QI Phase]@row = "Assess the Current State"), "Less than 25%")
This means if the value in [QI Phase]@row is equal to "Initiate Project"
OR the value in [QI Phase]@row is equal to "Assess the Current State"
return the text "Less than 25%".
It looks like this:
Then create a similar function for the next bracket:
=IF(OR([QI Phase]@row = "Design the Future State", [QI Phase]@row = "Test and Implement"), "25%50%")
I've only pasted this into row 3 here:
Then, when you can see that it works you can join (nest) the two formula together by pasting the second one into the first where the value_if_false would be. This means that if the first logic is not true, the second IF will be evaluated. The part in bold is the change to formula one. Don't miss the comma after the first value_if_true.
=IF(OR([QI Phase]@row = "Initiate Project", [QI Phase]@row = "Assess the Current State"), "Less than 25%", IF(OR([QI Phase]@row = "Design the Future State", [QI Phase]@row = "Test and Implement"), "25%50%"))
This can then be used in all of the rows:
Repeat this for the next two brackets
 Make an IF statement
 Test it works
 Nest it into the existing formula in the position for the value_if_false
Answers

Hi @SadeMJB
Start with an IF OR function like this:
=IF(OR([QI Phase]@row = "Initiate Project", [QI Phase]@row = "Assess the Current State"), "Less than 25%")
This means if the value in [QI Phase]@row is equal to "Initiate Project"
OR the value in [QI Phase]@row is equal to "Assess the Current State"
return the text "Less than 25%".
It looks like this:
Then create a similar function for the next bracket:
=IF(OR([QI Phase]@row = "Design the Future State", [QI Phase]@row = "Test and Implement"), "25%50%")
I've only pasted this into row 3 here:
Then, when you can see that it works you can join (nest) the two formula together by pasting the second one into the first where the value_if_false would be. This means that if the first logic is not true, the second IF will be evaluated. The part in bold is the change to formula one. Don't miss the comma after the first value_if_true.
=IF(OR([QI Phase]@row = "Initiate Project", [QI Phase]@row = "Assess the Current State"), "Less than 25%", IF(OR([QI Phase]@row = "Design the Future State", [QI Phase]@row = "Test and Implement"), "25%50%"))
This can then be used in all of the rows:
Repeat this for the next two brackets
 Make an IF statement
 Test it works
 Nest it into the existing formula in the position for the value_if_false

Thank you SO much!!! This worked!

Thanks for letting me know!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.4K Get Help
 321 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!