Microsoft Excel: Count Cells Between Dates

If you want to calculate how many cells within a range fall between certain dates, you can't go far wrong by using the COUNTIFS function. You'll need to add some clever stuff to it but it's an efficient and effective way of analysing your data. Want to know more? Read on!

Make sure you download the practice file to try this one out!

Dates Between

.xlsx

Download XLSX • 15KB

---

Here's a basic COUNTIFS function outline:

=COUNTIFS(Range1,Criteria1,Range2,Criteria2,...)

You can add up to 127 pairs of range and criteria to a COUNTIFS function!

To solve our query, we will introduce the & function into the fix. Our COUNTIFS will actually look like this:

=COUNTIFS(Range,">="&Date1,Range,"<="&Date2)

---

This is what this means:

Range

This is the range of dates you want to evaluate. You'll see that it's been used twice in our calculation and in each instance, this will be the same data set.

">="

This is the start of our first criteria. We are asking Excel to check for dates that are 'Greater than or equal to' by including these mathematical symbols in inverted commas. Think of this as the command section of our first criteria.

&

We are joining the command above to the next part of our criteria by using concatenation.

Date1

To finish up the first criteria, we will reference our first date. For example, this might be the first date in a year or in a month.

Range

Use the same range as before here.

"<="

This is the start of our second criteria. We are asking Excel to check for dates that are 'Less than or equal to' by including these mathematical symbols in inverted commas. Think of this as the command section of our second criteria.

&

Once again, we are joining the command above to the next part of our criteria by using concatenation.

Date2

We will finally reference our second date to complete our second criteria. This might be the last date in a year or in a month.

---

Let's use the practice spreadsheet to create our final calculation.

In H4, create the following calculation:

=COUNTIFS($B:$B,">="&$F4,$B:$B,"<="&$G4)

The result of this calculation should be 7.

We can complete our summary table by using the fill handle as we have used cell addressing correctly.

Then hide the start and end date columns to tidy things up.

---

Want to learn more about functions and formulas in Microsoft Excel?

Then book a place on my Essential webinar! Head to my Events page for more details!