Multiple IF statements using a dropdown

Options

Hi all.

Using a dropdown column with values: 0, 1-10. 20-100, can you use multiple IF statements in a formula in another column (cell) that states - IF cell = 0 then value equals 1 & IF cell = 1-10 then the value must equal 20 & IF cell = 20-100 then the value equals 50.

Is this possible??

Any assistance would be greatly appreciated.

Best Answer

  • saurabhmaheshwari
    Answer ✓
    Options

    Hi @Trav25

    If I understand your question correctly, then a nested if formula should work in your case. Here is an example

    1. Sheet with one column with drop down value named as "Drop Down" having values - 0, 1-10, 20-100
    2. Another column with formula named as "Formula Column"
    3. Use formula in "Formula Column" as "=IF([Drop Down]@row = 0, 1, IF([Drop Down]@row = "1-10", 20, IF([Drop Down]@row = "20-100", 50)))"

    Refer screenshot


    Let me know if this is correct representation or if you have a different scenario.

    Regards,

    Saurabh Maheshwari

    Smartsheet Superstar

    Portfolio Lead, TCS

Answers

  • saurabhmaheshwari
    Answer ✓
    Options

    Hi @Trav25

    If I understand your question correctly, then a nested if formula should work in your case. Here is an example

    1. Sheet with one column with drop down value named as "Drop Down" having values - 0, 1-10, 20-100
    2. Another column with formula named as "Formula Column"
    3. Use formula in "Formula Column" as "=IF([Drop Down]@row = 0, 1, IF([Drop Down]@row = "1-10", 20, IF([Drop Down]@row = "20-100", 50)))"

    Refer screenshot


    Let me know if this is correct representation or if you have a different scenario.

    Regards,

    Saurabh Maheshwari

    Smartsheet Superstar

    Portfolio Lead, TCS

  • Trav25
    Trav25 ✭✭✭✭
    Options

    Hi @saurabhmaheshwari,

    Thanks for the quick response, that's what I had used, but realised it was an error in my formula.

    Thanks so much!

    Trav

  • saurabhmaheshwari
    Options

    Glad to know that it worked.

    Regards,

    Saurabh Maheshwari

    Smartsheet Superstar

    Portfolio Lead, TCS

  • CarlaT
    Options

    Hello there,

    this answer has helped me greatly but I am struggling to extend this to the number of values I have in my dropdown. I've tried adding more, copying and pasting and adding a bracket but I keep getting the unparseable error.

    How would you go on to extend this to include more dropdown values - what am I missing?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @CarlaT

    Can you post the formula you've been trying, a list of your dropdown values, and an explanation of what you want to happen in each instance? We'd be happy to help you with this!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!