# Using IF(AND function with multiple conditions/criteria

Options
✭✭✭✭

So I'm trying to set a point value for when a selection is made (like metric scoring), but I have to assign two point-systems depending on what tier is answering, since the points will change depending on the tier. I was able to get the first selection to successfully calculate by:

=IF(AND([Tier Level]@row = "Tier 1", [CAPAs Closed on Time %]@row <= 79), 3, 5)

Since there are only two tiers, I was able to assign the 3pts for tier 1, and therefore my false value (for tier 2) would be 5pts. And this worked!

However, I need to finish the scale and add the rest of the points:

=IF(AND([Tier Level]@row = "Tier 1", [CAPAs Closed on Time %]@row <= 89), 5, 10)

=IF(AND([Tier Level]@row = "Tier 1", [CAPAs Closed on Time %]@row <= 99), 7, 15)

=IF(AND([Tier Level]@row = "Tier 1", [CAPAs Closed on Time %]@row = 100), 10, 20)

But anytime I add any further functions, I get an error. I've tried without the (=), by adding in (,) in between functions, but I'm not having any luck. Does anyone have a suggestion?

• ✭✭✭✭✭✭
Options

Using the Statement below, you have completed the IF formula.

=IF(AND([Tier Level]@row = "Tier 1", [CAPAs Closed on Time %]@row <= 79), 3, 5)

To nest this statement it would look more like this...

=IF(AND([Tier Level]@row = "Tier 1", [CAPAs Closed on Time %]@row = 100), 10, IF(AND([Tier Level]@row = "Tier 1", [CAPAs Closed on Time %]@row <= 99), 7, IF(AND([Tier Level]@row = "Tier 1", [CAPAs Closed on Time %]@row <= 89), 5, 10)))

This first IF says if Tier 1 and 100, then 10, otherwise move to the next IF statement.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!