Using IF(AND function with multiple conditions/criteria
So I'm trying to set a point value for when a selection is made (like metric scoring), but I have to assign two pointsystems 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?
Answers

To use nested IF Statements, either your True answer or your False Answer must be the start of the next IF statement.
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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 352 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 443 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!