You can use the following formula to count the number of cells in Excel that are less than a particular date:
=COUNTIF(A2:A11, "<"&D2)
This particular formula counts the number of cells in column A where the date is less than the date in cell D2.
The following example shows how to use this formula in practice.
Example: COUNTIF Less Than Date in Excel
Suppose we have the following dataset in Excel that shows the number of sales made by a company on various days:
Suppose we would like to count how many days are before 4/1/2022.
We can specify this date value in cell D2 and then type the following formula in cell E2:
=COUNTIF(A2:A11, "<"&D2)
The following screenshot shows how to use this formula in practice:
We can see that there are a total of 6 days in column A that occur before 4/1/2022.
Note that if we change the date in cell D2, the value in cell E2 will automatically updated.
For example, suppose we change the date to 3/1/2022:
We can see that there are a total of 4 days in column A that occur before 3/1/2022.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
COUNTIF vs. COUNTIFS in Excel: What’s the Difference?
How to Use COUNTIF with OR in Excel
How to Use COUNTIF From Another Sheet in Excel
