Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Sum values from drop down options
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
-
Remove the quotes from around the 1. Putting quotes around it generates a text value as opposed to a numerical value.
-
I tried that. I still get a zero. :-/
-
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!
-
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", " ")))))
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to e…
- Hello! Long time listener, first time caller — We have a weekly update dashboard that is presented on Thursdays. We'd like to have the date displayed be either the previous weeks Thursday or the curre…
- Can a JOIN function be used in a Sheet Summary to join two or more Sheet Summary Fields? I have two separate columns that I use a JOIN function for, but I'm not seeing how I can use it in Sheet Summar…
