Recuperación de valores del menú desplegable de selección múltiple en Excel

Question

Estoy intentando hacer cálculos desde mi menú desplegable. Tengo el siguiente menú desplegable en la hoja 1 de mi Excel.

## Category ##
### AAA ###
### BBB ###
### CCC ###
### DDD ###

En la hoja 2, tengo los valores correspondientes para este menú desplegable.

## Category  Category Value##
### AAA    1###
### BBB    2###
### CCC    3###
### DDD    4###

Agregué código VBA para selección múltiple y también agregué VLOOKUPuna fórmula simple para recuperar el valor de la categoría.

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

Con el código VBA, puedo seleccionar las tres categorías y también eliminar la categoría seleccionada más adelante. Pero no puedo recuperar la suma de la categoría seleccionada. Por ejemplo, si un cliente elige las categorías AAA y CCC, debería poder ver la suma como 4. Además, si un cliente primero elige las tres categorías y luego elimina una de ellas, entonces la suma debería actualizarse. No entiendo cómo actualizo mi VLOOKUPfórmula para obtener la suma.

Aquí está mi código VBA para selección múltiple.

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

=SUM(IF(ISERR(FIND(Sheet2!$I$2:$I$5;A1;1));0;Sheet2!$J$2:$J$5))

Esto debe funcionar, pero no es una fórmula normal. Es unformaciónfórmula. Para que funcionen las fórmulas matriciales, se ingresanocon Enter, sino que utilice una combinación de Ctrl++ .ShiftEnter

Además, cambie A1a su celda desplegable real.