Excel How to Count Cells between dates

This tutorial demonstrates how to count the number of date cells that fall between two specific dates using COUNTIFS. We will start with a simple exercise using a single condition and gradually progress to more complex exercises involving multiple conditions and comparison operators. By the end of this tutorial, you will be able to perform this task effortlessly and efficiently.

Video

Walkthrough

We will learn how to use the COUNTIFS function to count the cells between two dates using the following exercises. Let’s jump in.

Exercise 1: Counting Cells with a Single Condition

Let’s just get warmed up with COUNTIFS with a basic single condition.

Consider the worksheet below:

If we wanted to count the number of A cells in the range B11:B20, we could enter the following formula:

=COUNTIFS(B11:B20,"A")

B11:B20 is the range that contains the cells we want to count, and “A” is the criteria. Since the condition is text, we enclose it in quotes.

We hit enter and bam:

So, that is the basic idea with COUNTIFS … it counts the number of cells that meet one or more conditions. Now that we are warmed up with a single condition, let’s move to the next exercise where we will use multiple conditions.

Exercise 2: Multiple Conditions and Comparison Operators

Consider the following worksheet:

Let’s say we wanted to count the number of cells between 90 and 100. We could enter the following formula:

=COUNTIFS(B11:B24,">=90",B11:B24,"<=100")

You’ll notice there are 2 pairs of arguments, one pair for each condition. The first condition is greater than or equal to 90, all enclosed in quotes. The second condition is less than or equal to 100, all in quotes.

We hit enter and bam:

And that is how we use multiple conditions along with the greater than and less than comparison operators. Let’s bring these concepts together and do what we are here to do: count the number of cells between two dates.

Exercise 3: Counting Cells with Multiple Conditions and Cell References

Consider the following worksheet:

We’d like to be able to enter two dates into some cells, and have Excel count the number of data cells that fall between them. For example, we could enter the date range into cells C6 and C7:

Then, we could write the following formula in B8 to do the counting:

=COUNTIFS(B12:B22,">="&C6,B12:B22,"<="&C7)

The formula uses the COUNTIFS function with two conditions.

• The first pair of arguments defines the first condition.
  • It needs to include the cells in the range B12:B22 that are greater than or equal to the date entered in C6.
  • Note the comparison operator is enclosed in quotes “>=” as our previous exercise.
  • And, note that the comparison operator is joined to the cell reference C6 with the concatenation operator the ampersand &.
• The second pair of arguments creates the second condition.
  • Include those cells where the date is less than or equal to the date in C7.

We hit Enter, and bam:

What is nice is that we can easily enter new dates in C6 and C7 and the formula will update the count accordingly.

Conclusion

And that my friend is how you count the number of cells that fall between two dates with COUNTIFS. Hope it helps! And if you have any suggestions, improvements, alternatives, or questions, please post a comment below!

Sample file

FAQ

Q: Can I use COUNTIFS to count cells that fall between specific times instead of dates?

A: Yes, COUNTIFS can operate on cells that contain times or date/time value. is specifically designed to work with date values. Simply use the same steps as the Exercise 3 above, except use time values in the cells instead.

Q: What if the range of cells I want to count is not contiguous?

A: If the range of cells you want to count is not continuous, you can use multiple COUNTIFS functions in the formula using this syntax: =COUNTIFS(…)+COUNTIFS(…).

Q: Can COUNTIFS be used with non-date values?

A: Yes, you can use COUNTIFS to count cells with non-date values as long as you specify the appropriate criteria. For numeric values, use comparison operators like “>”, “<“, “>=”, “<=”, etc. For text values, enclose the criteria within double quotes (e.g., “apple”, “banana”, etc.).

Q: Are there any limitations to the number of conditions that can be used with COUNTIFS?

A: Yes, the COUNTIFS function supports up to 127 pairs of arguments (127 conditions).

Q: Is it possible to use COUNTIFS with wildcard characters to count cells with varying values?

A: Yes, COUNTIFS supports wildcard characters such as asterisk (*) and question mark (?). The asterisk represents any number of characters, while the question mark represents a single character. For example, to count cells where Column A starts with “ABC” and ends with any two characters, you can use the formula: “=COUNTIFS(A1:A10, “ABC??”)”.

Q: Can COUNTIFS be used to count cells based on criteria that includes OR logic?

A: For cells to be included in the count returned by COUNTIFS, all conditions must be true. In other words, it uses AND logic. To use OR logic instead, you could use one COUNTIFS function for each condition in a single formula. For example, to count cells where Column A contains “Apples” or “Oranges”, you could use the formula: =COUNTIFS(A1:A10, “Apples”) + COUNTIFS(A1:A10, “Oranges”).