How to create IF formula with multiple conditions

SadeMJB

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 25-50%.

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

KPH

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

KPH

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

SadeMJB

Thank you SO much!!! This worked!

KPH

Thanks for letting me know!