Nested IF(AND OR(IF(AND Statement

Options
Caitlin Gaines
Caitlin Gaines ✭✭✭
edited 12/09/19 in Formulas and Functions

I am trying to identify which projects need committee review vs those that can be auto-approved.

I have 5 columns with the following selections:

- Currently Underway? - Checked/Unchecked (I want values that are unchecked/=0)

- Helper - Timeline - Returns Yes/No based on formula. (I want values that = Yes")

- Helper - Count of Selections - Formula that returns number of selections made in another cell. (I want selections >1)

- Projected Capital/Expense Costs - Text/Number Field (I am looking for values > 500,000)

- Helper - Fast Tracked - Formula that returns "Fast Tracked" or "Standard". (I want those that are standard to require committee approval)

**The trick is that the project must not be underway and at least 2 of the other columns must meet the criteria. I've created about 100 formulas already and most return incorrect argument/unparsable. The below formula runs, but isn't returning the correct value.

 

=IF(AND([Helper - Timeline]@row = "Yes", [Helper - Count of Selections]@row > 1), "Requires Committee Review", IF(AND([Helper - Timeline]@row = "Yes", [Projected Capital/Expense Costs]@row > "500,000"), "Requires Committee Review", IF(AND([Helper - Timeline]@row = "Yes", [Helper - Fast Tracked]@row = "Standard"), "Requires Committee Review", IF(AND([Helper - Count of Selections]@row > 1, [Projected Capital/Expense Costs]@row > "500,000"), "Requires Committee Review", IF(AND([Helper - Count of Selections]@row > 1, [Helper - Fast Tracked]@row = "Standard"), "Requires Committee Review", IF(AND([Projected Capital/Expense Costs]@row > "500,000", [Helper - Fast Tracked]@row = "Standard"), "Requires Committee Review", "Auto-Approved"))))))

Tags:

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!