Sum values from drop down options

Jeana

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!

Paul Newcome

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

Jeana

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

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

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

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).