How to calculate multiple item dropdown list

Question

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.

nikolinode · Answer

Handiman&nbsp;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 :))&nbsp;&nbsp;I would be happy to know if I could help with this information.&nbsp;NikolinoI know I don't know anything (Socrates)

hansvogelaar · Answer

Handiman&nbsp;
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 &amp; Price List'!$B$2:$C$18)
Fill down.
See the attached version.

Forum Discussion

How to calculate multiple item dropdown list

2 Replies

Resources