Being able to count cells that fall in a date range in Excel is an important skill. In many cases, for example, you’ll need to count how many sales happened over a period of time. Or perhaps, you need to know how often a specific user logged into an application during a specific date range. Thankfully, Excel provides many different ways in which to count cells that fall within a specific date range. In this tutorial, you’ll learn how to count cells that are in a specific date range in Excel, using the COUNTIF() and COUNTIFS() function.
By the end of this tutorial, you’ll have learned the following:
- How to use the COUNTIF() and COUNTIFS() functions to count cells in a date range in Excel
- How to count cells before or after a given date in Excel
- How to count cells that fall in a given date range in Excel
How to Use Excel COUNTIFS() to Count Cells in a Date Range
How can you count cells that fall in a date range in Excel?
To count the number of cells that fall between a date range in Excel, you can use the Excel COUNTIFS() function. As your first criterion, pass in >=START_DATE. As your second criterion, pass in <=END_DATE. You can use the following formula: =COUNTIFS(range, <=START_DATE, range, >=END_DATE).
In order to count the number of cells that fall in a date range, we can use the Excel COUNTIFS() function. The function lets you count cells that meet multiple criteria. In this case, we want to count cells that are greater than a given date and less than another date.
Let’s take a look at an example of how to count cells using COUNTIFS() that fall into a certain date range:
Let’s break down what we’re doing in the example above:
- We used the Excel COUNTIFS() function to count cells based on two conditions.
- We used the same range of cells for both cells, B3:B11.
- For the first criterion, we used
">="&F2, indicating that the date should be greater than or equal to the date in cell F2. - For the first criterion, we used
"<="&F3, indicating that the date should be less than or equal to the date in cell F2.
Make note of wrapping the operator in double quotes. Without this, Excel will throw an error. If you simply want to check whether or not a date is between dates, you can do this quite easily in Excel.
In the following section, you’ll learn how to use Excel to count cells that are after a certain date.
How to Use Excel COUNTIF() to Count Cells After a Certain Date
Similar to the example above, you can use Excel to count cells that are on or after a certain date. For this, since we’re checking only a single condition, we can use the Excel COUNTIF() function. We could also use the COUNTIFS() function, though it’s a bit clearer to any future readers of your formula that you’re only searching for a single criterion.
Let’s take a look at an example where we count the number of cells that are after June 1, 2023:
In the example above, we used the following formula:
=COUNTIF(B3:B11, ">="&F2)This formula checks the range of B3:B11 to see which values are greater than or equal to the value in cell F2. Because Excel stores dates as numbers, we’re able to make this comparison very easily. Keep in mind that Excel will throw an error if the criteria isn’t wrapped in double quotes.
How to Use Excel COUNTIF() to Count Cells Before a Certain Date
Similar to the example above, it’s quite simple to count the number of cells that are on or before a given date. For this, we can also use the COUNTIF() function in Excel. In this case, however, we’ll use the <= operator, which represents less than or equal to.
Let’s take a look at an example, where we use the COUNTIF() function to count how many cells are on or before June 1, 2023.
In the example above, we use a very similar approach compared to counting cells on or after a certain date. However, we use the less than or equal to operator, <=, instead. By using the following formula, we’re able to count how many cells are on or before a certain date:
=COUNTIF(B3:B11, "<="&F2)Since Excel stores dates as integer values, we’re able to use simple, logical operators to count date values.
Conclusion
In this tutorial, you learned how to use Excel to count the number of cells that fall in a given date range. Being able to count cells in Excel is a common task, especially when counting cells conditionally. Knowing how to do this allows you to count how many sales have occurred since a given date, or how many users have logged in during a date range. Being able to set this up using Excel formulas allows you to count more reliably and robustly. It also gives you the ability to easily adapt to different dates or date ranges without needing to redo much work.
Additional Resources
To learn more about related topics, check out the resources below: