Calculating Values Based on Multiselect Dropdown Column
Hi, all!
I am building a sheet that helps users calculate the amount of effort (in hours) it would take to develop any combination of deliverables (training deliverables, in our case). In my sheet, I have a "Training Deliverables" multi-select drop-down column with a fairly long list of possible deliverables (see screenshot). Each deliverable is associated with a value (i.e., any of the "QSG-S" codes are 5 hours, any of the "QSG-M" codes are 25 hours, etc.).
What I'm trying to do: I'd like to be able to select any combination of the possible drop-down options and have a formula automatically sum and display their respective values in an adjacent cell/column. For instance, if I select a QSG-S code and a QSG-M code (as shown in the screenshot), and all QSG-S codes are worth 5 hours and all QSG-M codes are worth 25 hours, the SUM should be 30, and I'd like this SUM to display in an adjacent cell. In practice, I should be able to select any combination of codes in the list, and I'd like a formula to compute the SUM. Please let me know if this is possible to do. I assume I may need a few helper columns to build this formula. Thanks!
Answers
-
Good evening/morning,
Correct, you would need two helper columns. One for the training code(TrainingCode) and then one for the corresponding hours(Hours).
Formula would be like:
=SUMIFS((Hours:Hours), (TrainingCode:TrainingCode), HAS([Training Deliveriables]@row, @cell)))
Hope that works for ya!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!