IF Formula with Multiple Criteria
Hello! I have a software enhancement sheet, and I am trying assign priority to each enhancement based on the level of effort (LOE) + change priority calculated score.
The LOE can be one of the following: 1  Unknown, 2  High, 3  Medium, 4  Low, 5  None
The change score change range from 425
The breakdown for priority is:
 If LOE is low, but the change score is >= 19, first priority or first wave
 If LOE is medium, but change socre >= 19, second priority
 If LOE is low or medium, and change score is 1018, third priority
 If LOE is high, and change score >=19, fourth priority
 If LOE is high, and change score is 1018, fifth priority
 All remaining enhancements would be sixth priority.
I started trying to create a formula for the above breakdown. However, I am not getting the expected results. What I have so far is: =IF(AND([Level of Effort]@row = "2  High", [Change Priority  Calculated]@row <= 10), "Sixth Wave", IF(AND([Level of Effort]@row = "2  High", [Change Priority  Calculated]@row >= 19), "Fourth Wave", "Fifth Wave")) + IF(AND([Level of Effort]@row = "3  Medium", [Change Priority  Calculated]@row <= 10), "Sixth Wave", IF(AND([Level of Effort]@row = "3Medium", [Change Priority  Calculated]@row >= 19), "Second Wave", "Third Wave"))
But this is what is happening:
I don't understand why it's bringing back two values when it should just be "Fourth Wave." Any advice would be greatly appreciated!
Best Answer

You closed the IF statement and started another. It should be one continuous statement where the only time the false statement doesn't contain an IF is the last one.
=IF(AND([Level of Effort]@row = "4  Low", [Change Priority  Calculated]@row >= 19), "First Wave", IF(AND([Level of Effort]@row = "3  Medium", [Change Priority  Calculated]@row >= 19), "Second Wave", IF(OR([Level of Effort]@row = "4  Low", [Level of Effort]@row = "3  Medium"), IF(AND([Change Priority  Calculated]@row >= 10, [Change Priority  Calculated]@row <= 18), "Third Wave", "Sixth Wave"), IF(AND([Level of Effort]@row = "2  High", [Change Priority  Calculated]@row >= 19), "Fourth Wave", IF(AND([Level of Effort]@row = "2  High", [Change Priority  Calculated]@row >= 10), "Fifth Wave", "Sixth Wave")))))
Answers

You closed the IF statement and started another. It should be one continuous statement where the only time the false statement doesn't contain an IF is the last one.
=IF(AND([Level of Effort]@row = "4  Low", [Change Priority  Calculated]@row >= 19), "First Wave", IF(AND([Level of Effort]@row = "3  Medium", [Change Priority  Calculated]@row >= 19), "Second Wave", IF(OR([Level of Effort]@row = "4  Low", [Level of Effort]@row = "3  Medium"), IF(AND([Change Priority  Calculated]@row >= 10, [Change Priority  Calculated]@row <= 18), "Third Wave", "Sixth Wave"), IF(AND([Level of Effort]@row = "2  High", [Change Priority  Calculated]@row >= 19), "Fourth Wave", IF(AND([Level of Effort]@row = "2  High", [Change Priority  Calculated]@row >= 10), "Fifth Wave", "Sixth Wave")))))

Thanks, Devin! That worked beautifully!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!