Value retrieval from the Multiple selection dropdown in excel

Question

I am trying to make calculation from my dropdown menu. This is how my dropdown looks. Category

• AAA
• BBB
• CCC
• DDD

These are the values associated with my dropdown. Category Category value

• AAA 1
• BBB 2
• CCC 3
• DDD 4

I added VBA code for multiple selection and also added simple Vlookup formula to retrieve the value of category.

=VLOOKUP(E2;Sheet2!I2:J5;2;)

With the VBA code, i am able to select all three category and also remove the selected category later. But I am failing to retrieve the sum of selected category. For e.g. I want if customer chooses category AAA & CCC, he/she should be able to see sum as 4. Also if customer first chooses all three category and then removes one of the category then also the sum should get updated. I am not getting how do i update my Vlookup formula to get the sum.

Here is my VBA code for multiple selection.

Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated: 2016/4/12
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    If Target.Count > 1 Then Exit Sub
    On Error Resume Next
    Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Not Application.Intersect(Target, xRng) Is Nothing Then
        xValue2 = Target.Value
        Application.Undo
        xValue1 = Target.Value
        Target.Value = xValue2
        If xValue1 <> "" Then
            If xValue2 <> "" Then
                '                If xValue1 = xValue2 Or _
                '                   InStr(1, xValue1, ", " & xValue2) Or _
                InStr(1, xValue1, xValue2 & ",") Then
                If InStr(1, xValue1, xValue2 & ",") > 0 Then
                    xValue1 = Replace(xValue1, xValue2 & ", ", "") ' If it's in the middle with comma
                    Target.Value = xValue1
                    GoTo jumpOut
                End If
                If InStr(1, xValue1, ", " & xValue2) > 0 Then
                    xValue1 = Replace(xValue1, ", " & xValue2, "") ' If it's at the end with a comma in front of it
                    Target.Value = xValue1
                    GoTo jumpOut
                End If
                If xValue1 = xValue2 Then        ' If it is the only item in string
                    xValue1 = ""
                    Target.Value = xValue1
                    GoTo jumpOut
                End If
                Target.Value = xValue1 & ", " & xValue2
            End If
            jumpOut:
        End If
    End If
    Application.EnableEvents = True
End Sub

Answer 1

Instead of coding in VBA, you can simply use this function and it will work.

=SUMPRODUCT(ISNUMBER(SEARCH(Sheet2!A1:I4;Sheet1!A2))*Sheet2!B1:B4)