=ИНДЕКС($C$2:$C$9;НАИБОЛЬШИЙ(ЕСЛИ(ПОИСКПОЗ($C$2:$C$9;$C$2:$C$9;0)=СТРОКА($A$1:$A$8);СТРОКА($A$1:$A$8));СТРОКА()-1))

=СУММЕСЛИ($C$2:$C$9;G2;$B$2:$B$9)

=ИНДЕКС($A$2:$A$9;ПОИСКПОЗ(G2;$C$2:$C$9;0))

Option Explicit

Sub Otbor()
        Dim a(), oDict As Object, i As Long, temp As String
            
        a = Range("a1:c" & Range("C" & Rows.Count).End(xlUp).Row).Value

        Set oDict = CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(a)
            temp = Trim(a(i, 3))
            If Not oDict.Exists(temp) Then
                oDict.Add temp, CStr(a(i, 2))
            Else
                oDict.Item(temp) = CStr(--oDict.Item(temp) + a(i, 2))
            End If
        Next

      With ThisWorkbook.Worksheets(1)
          .Range("D1").Resize(oDict.Count) = Application.Transpose(oDict.keys)
          .Range("E1").Resize(oDict.Count) = Application.Transpose(oDict.items)
      End With

End Sub

Sub Otbor2()
     Dim Arr(), i&, temp$
     Arr = Range("A1:C" & Range("C" & Rows.Count).End(xlUp).Row).Value
     With Create_Object("Scripting.Dictionary")
        For i = 1 To UBound(Arr)
           temp = Trim(CStr(Arr(i, 3)))
           If Not .Exists(temp) Then
              .Add temp, CStr(Arr(i, 2))
           Else
              .Item(temp) = CStr(--.Item(temp) + Arr(i, 2))
           End If
        Next
        ThisWorkbook.Worksheets(1).Range("D1").Resize(.Count) = Application.Transpose(.Keys)
        ThisWorkbook.Worksheets(1).Range("E1").Resize(.Count) = Application.Transpose(.Items)
     End With
End Sub

Function TrArr(Arr)   'возвращает транспонированный 2D-массив
      Dim L1&, U1&: L1 = LBound(Arr, 1): U1 = UBound(Arr, 1)
      Dim L2&, U2&: L2 = LBound(Arr, 2): U2 = UBound(Arr, 2)
      Dim tArr(): ReDim tArr(L2 To U2, L1 To U1)
      Dim RR&, CC&
      For RR = L1 To U1
         For CC = L2 To U2
            tArr(CC, RR) = Arr(RR, CC)
         Next CC
      Next RR
      TrArr = tArr
End Function