CODEWITHSUNDEEP
excelvba
PP8
PP8

Reputation: 197

How to use COUNTIFS VBA Function to count value between two dates?

I would like to use VBA to count the number of times a value occurred between two given dates. The output should display 1.

I am using the following code but cant seem to get the correct value. 

Sub clientIntAnalysis()
Dim r As Integer
Dim startdate As Date, endDate As Date
startdate = "07/01/2019"
endDate = "07/30/2019"
    Dim LastCol As Long, LastRow As Long, rng As Range, rng2 As Range

    LastRow = Sheet3.Range("M" & Sheet3.Rows.Count).End(xlUp).Row
    LastCol = Sheet3.Cells(8, Columns.Count).End(xlToLeft).Column

    With Sheet3
        Set rng = .Range(.Cells(8, 14), .Cells(LastRow, LastCol))
        Set rng2 = .Range(.Cells(8, 13), .Cells(LastRow, LastCol))
    End With

    r = Application.WorksheetFunction.CountIfs(rng, ">=" & startdate, rng, "<=" & endDate, rng, "=" & "Client Interested") 'q3

    MsgBox r


End Sub

enter image description here

Upvotes: 0

Views: 1231

Answers (2)

PP8
PP8

Reputation: 197

Here is the working version of my code, looks like I had to increment the value of the last Column by 1.

Sub clientIntAnalysis()

Dim LastCol As Long, LastRow As Long, rng As Range, rang2 As Range, lastcol2 As Long, r as long

startdate = "07/01/2019"
enddate = "07/30/2019"
LastRow = Sheet3.Range("M" & Sheet3.Rows.Count).End(xlUp).Row
LastCol = Sheet3.Cells(8, Columns.Count).End(xlToLeft).Column
lastcol2 = Sheet3.Cells(8, Columns.Count).End(xlToLeft).Column + 1

Set rang2 = Sheet3.Range(Sheet3.Cells(8, 14), Sheet3.Cells(lastrow, lastcol2))
    With Sheet3
        Set rng = .Range(.Cells(8, 13), .Cells(LastRow, LastCol))
End With

r= Application.WorksheetFunction.CountIfs(rng, ">=" & startdate, rng, "<=" & enddate, rang2, "Client Interested") 'q3

    MsgBox r


End Sub

Upvotes: 0

Mikku
Mikku

Reputation: 6654

Use this code (Untested)

Now your ranges are as per you said in comment:

Sub clientIntAnalysis()

Dim r As Integer
Dim startdate As Date, endDate As Date
startdate = "07/01/2019"
endDate = "07/30/2019"


    Dim LastCol As Long, LastRow As Long, rng As Range, rng2 As Range

    LastRow = Sheet3.Range("M" & Sheet3.Rows.Count).End(xlUp).Row
    'LastCol = Sheet3.Cells(8, Columns.Count).End(xlToLeft).Column

    With Sheet3
        Set rng = .Range(.Cells(8, 13), .Cells(LastRow, 14))
        Set rng2 = .Range(.Cells(8, 14), .Cells(LastRow, 15))
    End With

    r = Application.WorksheetFunction.CountIfs(rng, ">=" & startdate, rng, "<=" & endDate, rng2, "=" & "Client Interested") 'q32

    MsgBox r


End Sub

Comment:=COUNTIFS(clientmenu!$M$8:$N$8,">="&"07/01/2019",clientmenu!$M$8:$N$8,"<="&"07/30/2019",clientmenu!$N$8:$O$8,"Client Interested")

Upvotes: 1

Related Questions