Sum totals from multi-select dropdown columns

Shannon E.

I have two columns with multi-select dropdowns. I would like to add a column that will sum the totals based on what is selected in the two dropdowns. Here is what I am trying to accomplish:

Cost List column: sandwich - $5.00; drink - $1.50

Optional Cost List column: icecream - $1.50; chips - $2.00

Total Cost to be charged: $10.00

What formula should I use to set this up to automate?

Paul Newcome

Will it only ever be 2 entries in both columns, or is the number of selections variable? If variable, what is the highest number of selections within a single cell?

Shannon E.

@Paul Newcome currently possible 7 selections from column 1 and 3 selections from column 2.

Paul Newcome

My suggestion would be a reference table that has each possible selection in one column and the price in another column. As long as the data in the "possible selections" column on the reference table match exactly what you have in the actual dropdown selection options, you can use a SUMIFS + SUMIFS like so:

=SUMIFS({Reference Table $$ Column}, {Reference Table Selection Column}, HAS(@cell, [1st Selection Column]@row)) + SUMIFS({Reference Table $$ Column}, {Reference Table Selection Column}, HAS(@cell, 2nd Selection Column]@row))