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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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).
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!