Calculating Values Based on Multiselect Dropdown Column

jefger

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!

TrevRCincySheetz

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!