Count Between Dates

Let’s say you measure something with a duration that can span multiple days. For example, a hotel guest can check in for multiple days, a patient can be in a hospital for multiple days, a widget can be in a specific manufacturing stage for multiple days, a book can be checked out for multiple days, or an order can be open for multiple days. Now, let’s say you need to get a daily count. For example, the number of guests in the hotel on any specific day. Or the number of patients, the number of widgets, the number of books, or the number of open orders. One way to do this is to use the COUNTIFS function with comparison operators. This post is inspired by a question from Michael … thanks Michael!

Objective

Before we get too far, let’s look at our objective.

In this example, we’ll count the number of hotel guests in a hotel on any given night. So, we have the data stored in Table1, which looks like this:

Then, we would like to determine the number of guests in the hotel each night, like this:

We will use COUNTIFS to accomplish this. Let’s dig in.

Video

Narrative

The COUNTIFS function counts the number of rows that meet a specified condition or conditions. It looks something like this:

=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)

Each condition is defined by a pair of arguments. Here is the narrative I think about when I use COUNTIFS:

“Count the number of rows where … this column [criteria_range1] is equal to this value [criteria1], and where this column [criteria_range2] is equal to this value [criteria2] …”

Assuming the date we are testing for is stored in B7, we write a formula like this just to get started (note: it isn’t our final working formula, this is just our first step):

=COUNTIFS(Table1[CheckIn], B7, Table1[CheckOut], B7)

But, here’s the thing. We aren’t testing to see which rows are EQUAL TO the criteria values. That is, which rows have a CheckIn date EQUAL TO B7 and a CheckOut date EQUAL TO B7. We are trying to determine if the guest checked in on or before B7 and checked out after B7. That is, where the CheckIn date is LESS THAN OR EQUAL TO B7 and where the CheckOut date is GREATER THAN B7.

The fancy term for this is comparison operators (>, <=, and so on).

There’s a special syntax for entering a comparison operator into the COUNTIFS function. In summary, we need to enclose it in quotes and then use concatenation to join the comparison operator with the cell reference. So, we would update our formula like this:

=COUNTIFS(Table1[CheckIn], "<=" & B7, Table1[CheckOut], ">" & B7)

Assuming we have created a list of the dates we want to test, we can simply fill that formula down in the adjacent column to get the GuestCt:

So, that is how to use COUNTIFS to count the number of items on a date when the duration spans multiple days. Hope it helps!

If you have any alternate approaches, please share by posting a comment below … thanks!

Sample file: CountBetweenDates.xlsx