how to use sumifs for date in vba

Question

I have the following code but it does not work.

Private Sub Worksheet_Activate()

Dim l As Long
Dim LastRow As Long
Dim oWkSht As Worksheet
Dim FirstDate As Date
Dim LastDate As Date
Dim tsales As Long

FirstDate = DateSerial(Year(Date), Month(Date), 1)
LastDate = DateSerial(Year(Date), Month(Date) + 1, 0)
LastRow = Range("A100000").End(xlUp).Row

Sheets(“Main”).Range("O2").Formula = "=SumIfs(Old_Products!Z2:Z & LastRow,Old_Products!O2:O  & LastRow, “ >= ” & FirstDate, Old_Products!O2:O12, “ <= ” & LastDate)+Registration!Z2:Z & LastRow"

End sub

I trying to sum all the values in worksheet Registration column Z2 to LastRow and also if the date in worksheet Old_Products column O2 to LastRow is a date that is in current month then I want the corresponding value in column Z be count as well

Answer 1

If you are to use VBA, then you could use the WorksheetFunction.SumIfs , which is the VBA version to "=SumIfs.

So the code below, will put the value of the WorksheetFunction.SumIfs in Range("O2") in "Main" sheet, and it will sum up all the values on column "Z", where the dates in column "O" are between FirstDate and the LastDate.

Code

Option Explicit

Private Sub Worksheet_Activate()

'Dim l As Long  '<-- not used in this code
Dim LastRow As Long
Dim oWkSht As Worksheet
Dim FirstDate As Date
Dim LastDate As Date
'Dim tsales As Long  '<-- not used in this code

Set oWkSht = Worksheets("Old_Products")

FirstDate = DateSerial(Year(Date), Month(Date), 1)
LastDate = DateSerial(Year(Date), Month(Date) + 1, 0)

LastRow = oWkSht.Range("A100000").End(xlUp).Row

Sheets("Main").Range("O2").Value = Application.WorksheetFunction.SumIfs(oWkSht.Range("Z2:Z" & LastRow), oWkSht.Range("O2:O" & LastRow), ">=" & CLng(FirstDate), oWkSht.Range("O2:O" & LastRow), "<=" & CLng(LastDate))

End Sub

---

Edit 1: My preferred version, since you are looking to compare dates, you can directly store the FirstDate and LastDate As Long, and then later there's no need to use CLng.

Also, added an option to find the last day of the month using the EoMonth function.

Code

Option Explicit

Private Sub Worksheet_Activate()

' Dim l As Long  '<-- not used in this code
Dim LastRow As Long
Dim oWkSht As Worksheet
Dim FirstDate As Long
Dim LastDate As Long
' Dim tsales As Long  '<-- not used in this code

FirstDate = DateSerial(Year(Date), Month(Date), 1)
LastDate = WorksheetFunction.EoMonth(Date, 0)

Set oWkSht = Worksheets("Old_Products")
With oWkSht
    LastRow = .Range("A100000").End(xlUp).Row
    Sheets("Main").Range("O2").Value = Application.WorksheetFunction.SumIfs(.Range("Z2:Z" & LastRow), .Range("O2:O" & LastRow), ">=" & FirstDate, .Range("O2:O" & LastRow), "<=" & LastDate)
End With

End Sub