Table of Contents
The VBA COUNTIF function can be used to count the number of cells in a range that match a specific criteria. In this case, it can be used to count the number of cells in a range that have a date greater than a specified date. To use this function, you will need to know the syntax and the range of cells you want to count. After that, you can enter the criteria for the COUNTIF to filter out the dates that are greater than the specified date and the function will return the number of cells that meet the criteria.
You can use the following basic syntax in VBA to count the number of dates that are greater than a specific date:
Sub CountifGreaterDate()
Range("D2") = WorksheetFunction.CountIf(Range("A2:A10"), ">" & Range("C2"))
End Sub
This particular example counts the number of dates in the range A2:A10 that are greater than the date in cell C2.
The result is then assigned to cell D2.
The following example shows how to use this syntax in practice.
Example: Countif Greater than Date Using VBA
Suppose we have the following list of dates in Excel and we’d like to know the number of dates that are greater than 4/25/2023:
We can create the following macro to do so:
Sub CountifGreaterDate()
Range("D2") = WorksheetFunction.CountIf(Range("A2:A10"), ">" & Range("C2"))
End Sub
When we run this macro, we receive the following output:
Cell D2 tells us that there are 6 dates in the range A2:A10 that are greater than 4/25/2023.
Note that we can change the value in cell C2 and run the macro again to count the number of dates greater than a different specific date.
For example, suppose we change the date in cell C2 to 10/1/2023 and run the macro again:
We can see that there are 3 dates greater than 10/1/2023.