SUMIFs of SALES IN LISTBOX/COMBOBOX

[bryskie]

Good Day Ma'am/Sir.

I am making a code for which i have a worksheet 2columns Dates,Sales.
I want to make a userform which i can choose years in combobox and
display and sum all the sales within each month in the list box.

I manage to find a tutorial but it has some minor issue. it display only the month and all 0

Heres the code:

Private Sub UserForm_Initialize()
For i = 0 To 3
Me.ComboBox1.AddItem Format(Date, "YYYY") - i
Next i
Me.ComboBox1.Value = Format(Date, "YYYY")
End Sub



Private Sub ComboBox1_Change()
Me.ListBox1.Clear
For i = 0 To 11
A = Application.WorksheetFunction.EDate("1" & "/" & "January" & "/" & Me.ComboBox1.Value, i)
B = Application.WorksheetFunction.EoMonth("1" & "/" & "January" & "/" & Me.ComboBox1.Value, i)
Me.ListBox1.AddItem Format(A, "MMMM")
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Application.WorksheetFunction.SumIfs _
(Sheet1.Range("B:B"), Sheet1.Range("A:A"), _
">=" & A, Sheet1.Range("A:A"), "<=" & B)
Next i
End Sub

heres the pic.
https://drive.google.com/open?id=1xQhXCg0WuLCeN9Z-zfRfWgXJ1YuaK4cm

Thank You

 

[AlphaFrog]

Re: SUMIFs of SALES IN LISTBOX/COMBOBOX! HELP!

This is a guess. The problem may be your dates on the worksheet are text-dates and not serial dates. Sometimes when you import dates from a text file into Excel, they are not converted to serial dates and Excel treats them as text even though they "look" like dates.

If you select one of the dates on the worksheet, what is the date format as seen in the formula bar (not the cell)?

Can you do a test SumIfs formula on the worksheet to see the total for one month? Does it work?

 

[bryskie]

Re: SUMIFs of SALES IN LISTBOX/COMBOBOX! HELP!

got it sir. thanks

small problem is. how can i display the header in the from work sheet to listbox?

Thanks

 

[bryskie]

Re: SUMIFs of SALES IN LISTBOX/COMBOBOX! HELP!

why is that it didnt display the headers name in the worksheet in userforms listbox