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" multiselect dropdown column with a fairly long list of possible deliverables (see screenshot). Each deliverable is associated with a value (i.e., any of the "QSGS" codes are 5 hours, any of the "QSGM" codes are 25 hours, etc.).
What I'm trying to do: I'd like to be able to select any combination of the possible dropdown options and have a formula automatically sum and display their respective values in an adjacent cell/column. For instance, if I select a QSGS code and a QSGM code (as shown in the screenshot), and all QSGS codes are worth 5 hours and all QSGM 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
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!