Can I sum text values like numbers from a single cell?

[MrBadEXCEL]

Below is a screenshot of a workbook with a sample of what I am trying to do.
I have a sheet with a tab labeled "MATERIALS". The sheet contains a list of materials in column A and in column B is the corresponding value of the materials

On a second sheet with the tab labeled "PROJECTS" I have a column labeled "Project Materials". The cell A2 contains a dropdown list of all the materials referenced
in the MATERIALS sheet. When I select a material from the dropdown list it is added to cell A2 and separated with a comma. On my sheet example I have three materials
selected "Black Material, White Material, Red Material"

What I am trying to do is get the list of material I use for a project in cell A2 to sum up in cell B2. In the example the total cost should equal $6.50.

 

[Peter_SSs]

@MrBadEXCEL
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

You might also investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

 

[Marcelo Branco]

Maybe

B2
=SUMPRODUCT(--ISNUMBER(SEARCH(MATERIALS!A$2:A$10,A2)),MATERIALS!B$2:B$10)

M.