Rooming list

[Estela]

Hi! Please I need your help. I have created a rooming list for an event with say 120 guest. I have their check in and check out dates. I need to be able to calculate how many rooms I need for a particular night.

The guests are booked between 23 Jan until 28 Jan. Their exact dates varies when they are participating.

For example, in excel I have the guest name with check in and out dates (3 columns)

Guest Check in Date. Check out date
1. 24 Jan 2017 26 Jan 2017
2. 25 Jan 2017 28 Jan 2017

And so on..

I want to be able to calculate how many do I need on the night of 24, on the night on 25, until 27th Jan.

Appreciate the help.

Thanks,
Estela

 

[Eric W]

Welcome to the board.

Try this:

	A	B	C	D	E	F	G	H	I	J	K
1	Guest	Check In Date	Check Out Date			23-Jan-2017	24-Jan-2017	25-Jan-2017	26-Jan-2017	27-Jan-2017	28-Jan-2017
2	Al	24-Jan-2017	26-Jan-2017			1	4	5	5	5	0
3	Beatriz	25-Jan-2017	28-Jan-2017
4	Cal	23-Jan-2017	28-Jan-2017
5	Diego	27-Jan-2017	28-Jan-2017
6	Elayne	24-Jan-2017	25-Jan-2017
7	Frank	25-Jan-2017	28-Jan-2017
8	Giselle	26-Jan-2017	28-Jan-2017
9	Hank	24-Jan-2017	27-Jan-2017
10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet16​

Worksheet Formulas Cell Formula F2 =COUNTIFS($B:$B,"<="&F1,$C:$C,">"&F1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Put the dates in the top row. Put the formula in F2, then drag right. Hope this helps.

 

[Estela]

Welcome to the board.

Try this:

	A	B	C	D	E	F	G	H	I	J	K
1	Guest	Check In Date	Check Out Date			23-Jan-2017	24-Jan-2017	25-Jan-2017	26-Jan-2017	27-Jan-2017	28-Jan-2017
2	Al	24-Jan-2017	26-Jan-2017			1	4	5	5	5	0
3	Beatriz	25-Jan-2017	28-Jan-2017
4	Cal	23-Jan-2017	28-Jan-2017
5	Diego	27-Jan-2017	28-Jan-2017
6	Elayne	24-Jan-2017	25-Jan-2017
7	Frank	25-Jan-2017	28-Jan-2017
8	Giselle	26-Jan-2017	28-Jan-2017
9	Hank	24-Jan-2017	27-Jan-2017
10

<tbody>
</tbody>

Sheet16​

Worksheet Formulas Cell Formula F2 =COUNTIFS($B:$B,"<="&F1,$C:$C,">"&F1) <tbody> </tbody>

<tbody>
</tbody>

Put the dates in the top row. Put the formula in F2, then drag right. Hope this helps.

Hi Eric,

I seem to get an error when I try it on my sheet.

Check in	Check out
07-Dec	10-Dec
07-Dec	10-Dec
07-Dec	10-Dec
07-Dec	10-Dec

<colgroup><col span="3"></colgroup><tbody>
</tbody>

 

[Eric W]

What error? And what version of Excel do you have?