Search code examples
arraysgoogle-sheetssumsequencetranspose

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.

https://docs.google.com/spreadsheets/d/1RxUrdl-qsMmz84YELN_aNWgYT8q6fOMwiLMO2XgRviU/edit?usp=sharing


Solution

  • try:

    =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