How To Calculate Hotel Reservation Nights For An Event In Excel

This tutorial came from a recent request on how to calculate the number of room reservations needed each night for an event. I came up with two solutions, one using the IF and AND functions, and the other using COUNTIFS.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here I have a list of 30 attendees for a conference that runs from 9/1/17 to 9/9/17. Each may be checking in and out on different dates, so we need to calculate how many rooms we need to reserve for each night:

One method is to create a chart like this:

The formula we used for each cell in this chart is:

=IF(AND($B2< =E$1,$C2>E$1),1,0)

Basically, what this formula says is IF the check-in date is less than or equal to the date above, AND the check-out date is greater than the date above, then the attendee needs a room that night, so put a “1” in that cell, if not, insert a “0”.

Then at the bottom we just totaled the columns, and transferred those values to simple chart:

Since the concept of what we did was really a counting of instances based on two criteria, the alternative was to use the COUNTIFS function. So, in our small chart, you can see the formula we used to accomplish the same values without the need of the larger, more graphic list:

And, as you can see, the formula we used was:

=COUNTIFS($B$2:$B$31,”< ="&O2,$C$2:$C$31,">“&O2)

The criteria we used for the two ranges were the same as the two used in the AND formula in option #1.

So, if you need to see the rooms required for each participant, use method one, but if you only need the results, try option #2.

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

No comments yet.

Leave a Reply