# IF Formula with multiple choices and values

I am trying to write a formula that will have the following conditions:

If the "Number of Affected Applications" equals 0 to 1, then the value in the CALC column would be "1"

If the "Number of Affected Applications" equals 2 to 4, then the value in the CALC column would be "2"

If the "Number of Affected Applications" equals 5 to 7, then the value in the CALC column would be "3"

If the "Number of Affected Applications" equals 8 to 9, then the value in the CALC column would be "4"

If the "Number of Affected Applications" equals 10 or more, then the value in the CALC column would be "5"

I had the formula working when it was only 1 condition, but adding these conditions, I can't figure out.

• ✭✭✭✭✭✭

Good morning.

Thank you for the clarification.

Here is an updated formula:

=IF([Number of Affected Applications]@row = "0 to 1", "1", IF([Number of Affected Applications]@row = "2 to 4", "2", IF([Number of Affected Applications]@row = "5 to 7", "3", IF([Number of Affected Applications]@row = "7 to 9", "4", "5"))))

You do not need the "Error" statement in your formula above.

Frank Smith, PMP

Assistant Director | IT Special Projects Mgr.

Oregon Parks & Recreation Department

If my response helps, please mark it as an accepted answer. 😎

• ✭✭✭✭✭✭

Greetings @Katherine Simpson,

Here is an example of the formula:

=IF(OR([Number of Affected Applications]@row >= 0, [Number of Affected Applications]@row >= 1), "1", IF(OR([Number of Affected Applications]@row >= 2, [Number of Affected Applications]@row >= 4), "2", IF(OR([Number of Affected Applications]@row >= 5, [Number of Affected Applications]@row >= 7), "3", IF(OR([Number of Affected Applications]@row >= 8, [Number of Affected Applications]@row >= 9), "4", "5"))

So, if any of the four conditions are met, then the desired number otherwise 5.

I hope this gets you on the right track.

Frank Smith, PMP

Assistant Director | IT Special Projects Mgr.

Oregon Parks & Recreation Department

If my response helps, please mark it as an accepted answer. 😎

• ✭✭✭✭✭✭

If you are pulling both sets of data from the same sheet, the following would work:

=IF([Number of Affected Applications]@row <= 1, 1, IF([Number of Affected Applications]@row <= 4, 2, IF([Number of Affected Applications]@row <= 7, 3, IF([Number of Affected Applications]@row <= 9, 4, 5))))

If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

• I should have stated that the "0 to 1" is an option, not numbers. How would that change the formula?

• @Frank S. Can you please help me by modifying the formula you provided where the equals are text such as "0 to 1", etc. Not numbers?

• I have this, but it isn't working.

=IF([Number of Affected Applications]@row = "0 to 1", "1", "Error" IF([Number of Affected Applications]@row = "2 to 4", "2", "Error" IF([Number of Affected Applications]@row = "5 to 7", "3", "Error" IF([Number of Affected Applications]@row = "7 to 9", "4", "Error" IF([Number of Affected Applications]@row = "10 or more", "5", "Error")))))

• ✭✭✭✭✭✭

Good morning.

Thank you for the clarification.

Here is an updated formula:

=IF([Number of Affected Applications]@row = "0 to 1", "1", IF([Number of Affected Applications]@row = "2 to 4", "2", IF([Number of Affected Applications]@row = "5 to 7", "3", IF([Number of Affected Applications]@row = "7 to 9", "4", "5"))))

You do not need the "Error" statement in your formula above.

Frank Smith, PMP

Assistant Director | IT Special Projects Mgr.

Oregon Parks & Recreation Department

If my response helps, please mark it as an accepted answer. 😎

• Thank you so much! That works perfectly!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!