Google sheets – calculating price from multiple dropdown selections

I would like to calculate the total price in column N (Materials Price) based on the multiple dropdown selections in column M (Materials). How would I accomplish this? The materials and corresponding pricing are in the "price sheet" tab.

For example, in cell M7 there are 3 materials listed: Rollers 10,7 Foot Cable,Side Plates. I would like the total price of $51 to appear in cell N7,

Thank you in advance…I’m new to this so I hope my question is clear.

>Solution :


=ARRAYFORMULA(MMULT(IFNA(VLOOKUP(SPLIT(M7, ","), 'Price Sheet'!A:B, 2, 0), 0), 
 SEQUENCE(COLUMNS(SPLIT(M7, ",")), 1, 1, 0)))

enter image description here

you can even replace cell reference with a column range and get whole column by one formula

Leave a Reply