Need Help with Multiple If/Then Statements

I have spent hours researching Smartsheet Community posts, but cannot find a solution. I have a sheet that I need to enter a value based on information in other columns on the sheet. I have tried IF,OR,AND combinations. Below is the criteria:

-If less than 50,001, then Enter "Deputy Director"

-If greater than 50,000, then Enter "Director"

-If greater than 50,000 AND a column contains "UFD", then Enter "Director"


Here is the formula that works perfectly for the first two criteria listed above. The third criteria is not working.

=IF([Dollar Amount for this Option Only]@row < 50001, "DeputyDirector", IF([Dollar Amount for this Option Only]@row > 50000, "Director", IF([Required Action]@row, "UFD", "DeputyDirector")))

Answers

  • Victoria_Indimar
    Victoria_Indimar ✭✭✭✭✭

    Hello - What's the difference between your second and third criteria? It looks like regardless of UFD, as long as Dollar Amount for this Option Only 50000, the result is still Director. Is that correct?

    If there truly is a difference, such that 50000+ & UFD results in "Something Else" but if 50000+ but no UFD, then results in "Director", then can you try this?


    =IF ( AND(Contains("UFD",[Required Action]@cell),[Dollar Amount for this Option Only]@row>50000),"Something Else", IF ([Dollar Amount for this Option Only]@row >50000, "Director", "Deputy Director"))

  • Thanks for trying to help! The formula provided still did not work. The three criteria are all different. All rows with "UFD" go to the Deputy Director, regardless of dollar amount. All rows greater than $50,000 all go to the Director. All rows less than $50,001 go to the Deputy Director.

    -If less than 50,001, then Enter "Deputy Director"

    -If greater than 50,000, then Enter "Director"

    -If greater than 50,000 AND a column contains "UFD", then Enter "Director"

  • I finally figured out the correct formula. I wanted to post it here in case it can help others. Thank you, Victoria_Indimar, for your help in getting me on the right track! I truly appreciate your assistance!

    =IF(OR(CONTAINS("UFD", [Required Action]@row), [Dollar Amount for this Option Only]@row < 50001), "DeputyDirector", IF([Dollar Amount for this Option Only]@row > 50000, "Director"))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!