Sum values from drop down options

Jeana
Jeana ✭✭✭✭✭✭
edited 05/29/20 in Formulas and Functions

So I have a drop down column with options from 1--3. Each has a description similar to this:

Priority

1 - Nice to have

2 - Needed

3 - Required


I would like to total the actual number that is selected in a row below these options (then I'll total the row).

What would this formula look like? I've tried a simple if =IF(Priority@row = "Nice to have", "1", 0) and I keep getting 0. I don't want to count the number of selections, I just want the numerical value of the selection.

Thanks!

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Remove the quotes from around the 1. Putting quotes around it generates a text value as opposed to a numerical value.

  • Jeana
    Jeana ✭✭✭✭✭✭

    I tried that. I still get a zero. :-/


  • Jeana
    Jeana ✭✭✭✭✭✭

    I'll try to explain myself better, it's been a long day. :-)

    I want users to be able to select from a drop down menu in a particular cell. I'm trying a helper column with a formula that will put a number 1 in the cell if they pick the first option and a number 2 if they pick the second option and so on. I need to be able to sum the column with these numbers.

    Thanks in advance for any insight on this one!

  • Jeana
    Jeana ✭✭✭✭✭✭

    I ended up using a nested if in a Helper Column.

    =IF(Priority@row = "1 = Nice to have", "1", IF(Priority@row = "3 = Conference", "3", IF(Priority@row = "4 = Roadmap release", "4", IF(Priority@row = "5 = Strategic account", "5", IF(Priority@row = "6 = Contractual obligation", "6", " ")))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Sorry for the delayed response. Seeing your screenshot... The key is making sure the text inside of the quotes is a match.

    "Nice to have" is not going to count any cells that contain "1 = Nice to have".


    Depending on if you need to expand the number of selections or not (which could turn into a long formula that is hard to manage, you could also use...

    =VALUE(LEFT(Priority@row))


    If you go beyond single digit numbers...

    =VALUE(LEFT(Priority@row, FIND(" ", Priority@row) - 1))


    The above will pull the number from the beginning of the selection, so it doesn't matter what that number is. This avoids you having to spell a lot of things out if you have a lot of options and allows you to change the verbiage for the selections without having to change the formula itself (as long as there is always a space after the number).

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!