CODEWITHSUNDEEP
excelsumifsvba
Pourya Ashena
Pourya Ashena

Reputation: 49

how to use sumifs for date in vba

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

Upvotes: 0

Views: 2925

Answers (1)

Shai Rado
Shai Rado

Reputation: 33682

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

Upvotes: 2

Related Questions