Wrong date for sumif on userform

[tuine]

Hi everyone,
I got three columns for my spreadsheet.
column A is date in format of dd/mm/yyyy
column B is Customer name
column C is total sale for each customer.

I set up a userform to check total of sale for a customer from a certain date. for example I want to know how many items have been delivered to customer John from 04/11/2018.

My trouble is the code below not work properly. It did not pick up the correct date.
TextBox1 is where I enter the date. 4/11/2018
ComboBox1 is where I enter the customer name John

Could you please help to fix my code. Many thanks

Sub Sum_ifs()


Dim NumOfItem, Customer, DateRange As Range
Set NumOfItem = Range("C:C")
Set Customer = Range("B:B")
Set DateRange = Range("A:A")
S_date = UserForm1.TextBox1.Value


MsgBox WorksheetFunction.SumIfs(NumOfItem, Customer, UserForm1.ComboBox1, DateRange, ">=" & CDate(S_date))
End Sub

 

[JustynaMK]

Alternatively, you can achieve the same without using the helper column, but you'd need to modify your VBA code and skip SumIfs function:

Sub Sum_ifs()
[COLOR=#ff0000]    Dim NumOfItem, rngCell As Range[/COLOR]
    Dim S_date As Date
[COLOR=#ff0000]    Dim lngLastRow As Long[/COLOR]
[COLOR=#ff0000]    Dim lngResult As Long[/COLOR]
    
[COLOR=#ff0000]    lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row[/COLOR]

[COLOR=#ff0000]    Set NumOfItem = Range("C2:C" & lngLastRow)[/COLOR]
    
    S_date = UserForm1.TextBox1.Value
    
[COLOR=#ff0000]    For[/COLOR][COLOR=#ff0000] Each rngCell In NumOfItem[/COLOR]
[COLOR=#ff0000]        If IsNumeric(rngCell.Value) _[/COLOR]
[COLOR=#ff0000]            And rngCell.Offset(0, -1).Value = "John" _[/COLOR]
[COLOR=#ff0000]            And rngCell.Offset(0, -2).Value >= CLng(S_date) _[/COLOR]
[COLOR=#ff0000]            And rngCell.Font.ColorIndex = 3 Then[/COLOR]
[COLOR=#ff0000]                lngResult = lngResult + rngCell.Value[/COLOR]
[COLOR=#ff0000]        End If[/COLOR]
[COLOR=#ff0000]    Next rngCell[/COLOR]
[COLOR=#ff0000]
[/COLOR]
[COLOR=#ff0000]    MsgBox lngResult[/COLOR]


End Sub

 

[tuine]

JustynaMK, you are super, thanks very very much. Both are very good. As I have no idea how to do the first one, I am thinking about the second one and you give the solution right away. You are very smart.
??

 

[JustynaMK]

Thank you! & good luck!