How to calculate multiple item dropdown list

Occasional Visitor

I have a monthly invoice list of purchases.

  • I have a multiple item dropdown list in an abbreviated form. (Menu & Price List)
  • I need to have the short item name equal to an amount.
  • Then in the ledger sheet add all the items in the abbreviated dropdown list into a total.

I cannot convert the abbreviated item name to a number, and cannot get the multiple items in the dropdown list add to a total.

Please help, I have been stuck on this issue for ages.

2 Replies


Unlock file I did not understand exactly what was looking for, but here is a small suggestion for a solution. of what I think I understand :))



I would be happy to know if I could help with this information.



I know I don't know anything (Socrates)


Here is a custom function you can use:

Function Total(Items As String, PriceList As Range) As Double
    Dim a() As String
    Dim i As Long
    Dim t As Double
    a = Split(Items, ", ")
    For i = LBound(a) To UBound(a)
        t = t + Application.VLookup(a(i), PriceList, 2, False)
    Next i
    Total = t
End Function

Use like this in C4:

=Total(B4,'Menu & Price List'!$B$2:$C$18)

Fill down.

See the attached version.