# 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 4-25

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 10-18, third priority
• If LOE is high, and change score >=19, fourth priority
• If LOE is high, and change score is 10-18, 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 = "3-Medium", [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!

Tags:

• ✭✭✭✭✭

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")))))
```

• ✭✭✭✭✭

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!