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]

Hi tuine,

Try to add another Dim statement and change S_date data type to Long (number):
Dim S_date as Long

Then, in your SumIfs formula, replace ">=" & CDate(S_date) with ">=" & S_date.

Let me know if that worked for you.

 

[tuine]

Hi tuine,

Try to add another Dim statement and change S_date data type to Long (number):
Dim S_date as Long

Then, in your SumIfs formula, replace ">=" & CDate(S_date) with ">=" & S_date.

Let me know if that worked for you.

Thanks for your quick help, JustynaMK
It doesn't work. the error said type missmatch

 

[tuine]

Hi JustynaMK,
your suggest rings me a bell. After some testing it finally works with Dim S_date as DATE and the CDate should be CLng. I don't understand why but it works like that.

Dim NumOfItem, Customer, DateRange As Range
Dim S_date As Date
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, ">=" & CLng(S_date))

 

[JustynaMK]

awesome!

I also noticed that it only started to work on my side when Date was converted to a number. I'm not quite sure why it works like that, but SUMIF as function in worksheet appears to NOT be type explicit, which is why you can perform conditional calculation matching text numbers back to true numbers (and vice versa).
E.g.: =SUMIFS(C:C,B:B,"John",A:A,">="&"04/11/2018")

In VBA, as WorksheetFunction, however, the rules appears as though they may be different. The behaviour I observe so far seems consistent with cell value recognition being upon the Range.Value2 property, rather than Range.Value, and even Range.Text properties. That might be the reason why you need to use a number instead of a date.

 

[tuine]

awesome!

I also noticed that it only started to work on my side when Date was converted to a number. I'm not quite sure why it works like that, but SUMIF as function in worksheet appears to NOT be type explicit, which is why you can perform conditional calculation matching text numbers back to true numbers (and vice versa).
E.g.: =SUMIFS(C:C,B:B,"John",A:A,">="&"04/11/2018")

In VBA, as WorksheetFunction, however, the rules appears as though they may be different. The behaviour I observe so far seems consistent with cell value recognition being upon the Range.Value2 property, rather than Range.Value, and even Range.Text properties. That might be the reason why you need to use a number instead of a date.

Great! thanks very much for your explanation.

 

[tuine]

Could I please have another question?
If I want to add one more criteria for cell color what should I do?
for example in range C:C, some sale items are in red. I want to sum all sale items for John, from 4/11/2018 and those items are in red.
Is that possible?
many thanks

 

[JustynaMK]

Hi tuine,

It will be possible with VBA - please let me know if this is a font color or a fill color. Also, if these colors are created using Conditional Formatting (instead of doing it "manually"), then the matter is more complicated. In such case let me know what kind of Conditional Formatting formula is driving these formats.

 

[tuine]

Great to hear. It is normal font colour. Not conditional formatting . Thanks in advance, JustynaMK

 

[JustynaMK]

Thanks! One idea might be to simply add a helper column (column D) - this can be hidden for presentation purposes.

So in your VBA you need to add the following formula:

Public Function FontCol(rngCell As Range)


    FontCol = rngCell.Font.ColorIndex


End Function

Then in cells D2 & below calculate the following:
=FontCol(C2)
The result will be 1 for black and 3 for red. Now we need to modify your SUMIFS:

Dim NumOfItem, Customer, DateRange, ColorRange As Range
...
Set ColorRange = Range("D:D")
...
WorksheetFunction.SumIfs(NumOfItem, Customer, "John", DateRange, ">=" & CLng(S_date), ColorRange, 3)

Does it work for you?