How to use sumifs in Multi Column ComboBox in Excel VBA

 use sumifs in Multi Column ComboBox in Excel VBA

step 1: goto developer tab >> visual basic >> insert a userform >> design it

step 2: double click on userform >> change the macro name to initilize 

Step 3: write macro as follow

Private Sub UserForm_Initialize()
Dim sh As Worksheet
Set sh = Sheets("Sheet1")
Dim lastrow As Long
Dim R As Long
lastrow = sh.Range("C" & Rows.Count).End(xlUp).Row
For R = 2 To lastrow
If Application.WorksheetFunction.CountIf(sh.Range("C2", "C" & R), sh.Cells(R, 3)) = 1 Then
With Me.ComboBox1
.AddItem sh.Cells(R, 3)
.List(.ListCount - 1, 1) = Application.WorksheetFunction.SumIfs(sh.Range("D:D"), sh.Range("C:C"), sh.Cells(R, 3)) _
- Application.WorksheetFunction.SumIfs(sh.Range("E:E"), sh.Range("C:C"), sh.Cells(R, 3))

End With
End If
Next R

Call sorting_data
End Sub

step 4: write macro to sort data in combobox as alphabetically

Sub sorting_data()
Dim a As Long, b As Long, c As Variant, x As Long
With UserForm1.ComboBox1
For a = 0 To .ListCount - 1
For b = a To .ListCount - 1
If .List(a) > .List(b) Then
For x = 0 To 1
c = .List(a, x)
.List(a, x) = .List(b, x)
.List(b, x) = c
Next x
End If
Next b
Next a
End With
End Sub

step 5: write macro to transfer data from 2nd column of combobox to TextBox

Private Sub ComboBox1_Change()
Me.ComboBox1.DropDown
Dim p As Long
For p = 0 To Me.ComboBox1.ListIndex
If Me.ComboBox1.Value = Me.ComboBox1.List(p) Then
Me.TextBox1.Value = Me.ComboBox1.List(p, 1)
End If
Next p
End Sub 

 

For support & feedback:

Subscribe my channel & follow my blogspot.

To download this file Please click on the Download button.