# Combining IF and IF AND Formulas

Options
✭✭✭
edited 11/10/22

I have three formulas below that work as individual formulas. I am trying to combine them in my Status column into one formula so my cell automatically updates based on the percent shown in the % complete cell. I thought it should be =IF([% Complete]@row = 0, "Not Started"), IF(AND([% Complete]@row > 0.01, [% Complete]@row < 1), "In Progress"), IF([% Complete]@row = 1, "Complete") but that does not seem to work.

Basically, if the % complete is 0 then it should return "Not started", if it's between 1%-99/100% then it should return "In Progress" and if it's 100% it should return "Complete".

IF([% Complete]@row = 0, "Not Started")

IF(AND([% Complete]@row > 0.01, [% Complete]@row < 1), "In Progress")

IF([% Complete]@row = 1, "Complete")

I am confused because each formula works on its own but they do not work together. What am I doing wrong?

Tags:

• ✭✭✭✭✭✭
Options

Hi @Jodi C,

I automated my Status column the same way using this.

=IF([% Complete]@row = 0, "Not Started", IF(AND([% Complete]@row >= 0.01, [% Complete]@row < 1), "In Progress", "Complete"))

Hope this helps!

BRgds,

-Ray

• ✭✭✭✭✭✭
Options

Hi @Jodi C,

I automated my Status column the same way using this.

=IF([% Complete]@row = 0, "Not Started", IF(AND([% Complete]@row >= 0.01, [% Complete]@row < 1), "In Progress", "Complete"))

Hope this helps!

BRgds,

-Ray

• ✭✭✭
Options

@Ray Lindstrom Thank you!

• ✭✭✭✭✭✭
Options

For the formula to move to the second IF, the first must be false. For it to move to the third, the first and second must be false, so on and so forth.

Leveraging that (and assuming you won't have a negative %), you can actually simplify it a little bit by getting rid of the AND function along with the greater than or equal to .01 portion.

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

It doesn't make a huge difference in this specific case, but it is something to keep in mind when you start stacking more and more IFs on.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!