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
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!