Is there a function in sheets that can calculate the total of multiple item in the same field?

Question

Name	Price	Basket	Total
A	1	A/B/C	6
B	2	B/C	5
C	3	A/C	4
		A/A/B	4
		C/C/C	9

Column A and B are the name and value of each item and column C contains the list of one or multiple items and I would like to calculate the total in column D base on the item contained in column C and their respective prices.

I have tried multiple solution but to no avail, "=SUM(IFERROR(VLOOKUP(FLATTEN(SPLIT(JOIN("/", B:B), "/")), C:A, 2, FALSE), 0))"

Answer 1

Another alternative solution is by using XLOOKUP Function:

=ARRAYFORMULA(SUM(XLOOKUP(SPLIT(C2,"/"),A:A,B:B)))

Answer 2

I adapted your formula so that it uses the SPLIT on the specific cell in C, and use that value to VLOOKUP:

=SUM(IFERROR(INDEX(VLOOKUP(SPLIT(C2,"/"), A:C, 2, FALSE), 0)))

You can also use it as an array for the whole column:

=BYROW(C2:C,LAMBDA(each,IF(each="","",SUM(IFERROR(INDEX(VLOOKUP(SPLIT(each,"/"), A:C, 2, FALSE), 0))))))