Lookup Multiple Values In A Single Cell (separated By Commas) And Then Return The Sum of the Corresponding Values

[mkary]

So here is what my challenge is...

I have a list of equipment I use. I have the list created in a table from which i can select multiple values and it will place them into the same cell and separate them by commas (like seen below in column A). To do this I am using the following code:
"Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
'run code if only one cell was changed
If Target.Count > 1 Then GoTo exitHandler

Select Case Target.Column
Case 2 'this Case line works for column B only
'Case 2, 5, 6 'this Case line works for multiple columns
On Error Resume Next
'check the cell for data validation
Set rngDV = Target.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If oldVal <> "" Then
If newVal <> "" Then
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End Select
exitHandler:
Application.EnableEvents = True
End Sub"

Now what I want to happen when I select the items in Column A is for Column B (Cost) to sum the associated values from the adjacent table (Equipment/Price). Essentially what I want to get out of it is =sum(5.09+50.45+20.00+20.00) but without having to manually input as my actual list has ~100+ Items. Is this possible?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Equipment[/TD]
[TD]Cost[/TD]
[TD][/TD]
[TD][/TD]
[TD]Equipment[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Item 1, Item 3, Item 5, Item 5[/TD]
[TD]Example[/TD]
[TD][/TD]
[TD][/TD]
[TD]Item 1[/TD]
[TD]5.09[/TD]
[/TR]
[TR]
[TD]Item 2, Item 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Item 2[/TD]
[TD]4.99[/TD]
[/TR]
[TR]
[TD]Item 2, Item 3, Item 4, Item 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Item 3[/TD]
[TD]50.45[/TD]
[/TR]
[TR]
[TD]Item 1, Item 4, Item 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Item 4[/TD]
[TD]16.95[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Item 5[/TD]
[TD]20.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[jimrward]

assumimg your price data starts in E2 to Fn, try the following user defined function, I have been working on something similar at work, so this is a trimmed down version

Function ItemSum(myrange As Range)

Dim MyDelim As String

Dim LastPriceRow As Integer
Dim NumPrice As Integer

'
'****
' set delimiter
'****
'

MyDelim = ","

'
'****
' Load our prices, this creates a 2 dimensional array starting at 1
'****
'
Dim CurSheet As String
CurSheet = ActiveSheet.Name

LastPriceRow = Worksheets(CurSheet).Range("E" & Rows.Count).End(xlUp).Row
PriceLookup = Worksheets(CurSheet).Range("E2:F" & LastPriceRow)
NumPrice = UBound(PriceLookup)
MyPas = Split(myrange.Value, MyDelim)
mysum = 0
For p = 0 To UBound(MyPas)
    mysum = mysum + GetPrice(MyPas(p), PriceLookup)
Next p
ItemSum = mysum
End Function
Function GetPrice(PriceCode, PriceLookup) As String
NumPrice = UBound(PriceLookup)
LowPrice = LBound(PriceLookup)
PriceCode = LTrim(RTrim(PriceCode))
For g = LowPrice To NumPrice
    If PriceCode = PriceLookup(g, 1) Then
        GetPrice = PriceLookup(g, 2)
        Exit Function
    End If
Next g
End Function

in cell B2 put =itemsum(A2)

 

[mkary]

assumimg your price data starts in E2 to Fn, try the following user defined function, I have been working on something similar at work, so this is a trimmed down version

Function ItemSum(myrange As Range)

Dim MyDelim As String

Dim LastPriceRow As Integer
Dim NumPrice As Integer

'
'****
' set delimiter
'****
'

MyDelim = ","

'
'****
' Load our prices, this creates a 2 dimensional array starting at 1
'****
'
Dim CurSheet As String
CurSheet = ActiveSheet.Name

LastPriceRow = Worksheets(CurSheet).Range("E" & Rows.Count).End(xlUp).Row
PriceLookup = Worksheets(CurSheet).Range("E2:F" & LastPriceRow)
NumPrice = UBound(PriceLookup)
MyPas = Split(myrange.Value, MyDelim)
mysum = 0
For p = 0 To UBound(MyPas)
    mysum = mysum + GetPrice(MyPas(p), PriceLookup)
Next p
ItemSum = mysum
End Function
Function GetPrice(PriceCode, PriceLookup) As String
NumPrice = UBound(PriceLookup)
LowPrice = LBound(PriceLookup)
PriceCode = LTrim(RTrim(PriceCode))
For g = LowPrice To NumPrice
    If PriceCode = PriceLookup(g, 1) Then
        GetPrice = PriceLookup(g, 2)
        Exit Function
    End If
Next g
End Function

in cell B2 put =itemsum(A2)

Thanks for the quick response jimrward! Unfortunately I am having trouble getting it to work... I get a #NAME? error when I put =itemsum(A2) in cell B2. This may just be me being a bit slow as I am not particularily well versed in the coding and functions of excel but am very keen to learn it! Any further advise you could provide would be greatly appreciated!

Cheers,
Mike