Sum of multiple variables within a cell based on a key

[GuyGadois]

I have the following sheet. I am trying to find a simple solution where the user would input the Items (yellow shading) and the Sum column is automatically calculated based on the items listed in the Items column. The key will end up being closer to 10 items with associated costs. A client may have multiple items (i.e. A,A,A) which I would like to add up. They may also have up to ten items. Is I possible to add up all the associated 'costs' in the item fields?

Cheers,

GG

Client	Items	Sum		Key
Client #1	A	$ 10.00		Item	Cost
Client #2	A, B	$ 30.00		A	$ 10.00
Client #3	D	$ 40.00		B	$ 20.00
Client #4	D, C	$ 70.00		C	$ 30.00
				D	$ 40.00

 

[JoeMo]

Here's a UDF you can try. Install it, then use it like a worksheet function as in the example below:

Book1
	A	B	C	D	E	F
1	Client	Items	Sum		Key
2	Client #1	A	10		Item	Cost
3	Client #2	A, B	30		A	10
4	Client #3	D	40		B	20
5	Client #4	D, C	70		C	30
6	Client #5		0		D	40
7	Client #6	X, Y, Z	#N/A
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=SumKeys(B2)

Function SumKeys(S As String) As Variant
Dim Vitem As Variant, Vsum As Variant, i As Long, j As Long, ct As Long
Vitem = Range("E3:E6").Value
Vsum = Split(S, ", ")
For i = LBound(Vsum) To UBound(Vsum)
    For j = LBound(Vitem, 1) To UBound(Vitem, 1)
        If InStr(1, Vsum(i), Vitem(j, 1)) > 0 Then
            ct = ct + 1
            If ct = UBound(Vsum) + 1 Then
                GoTo Nx1
            Else
                GoTo Nx2
            End If
        
        End If
    Next j
Nx2:
Next i
Nx1:
If ct <> UBound(Vsum) + 1 Then
    SumKeys = CVErr(xlErrNA)
    Exit Function
End If
For i = LBound(Vsum) To UBound(Vsum)
    SumKeys = SumKeys + Application.Lookup(Vsum(i), Range("E3:E6"), Range("F3:F6"))
Next i
End Function