Hotel Booking Rooms Count

[babar1988]

I am using an excel file to count the hotel reserved room counts by entering the reservation details, for example:

if guests reserved 25th till 30th 2 double, it should the total rooms consumed from 25th till 30th, each day 2 units

if another guest booked from 25th till 27th 5 double rooms, then it means the total from 25th till 27th, the room reserved 7 and from 28 till 30th 2 rooms.

Room Chart-updatedrooms.xlsx
	A	B	C	D	E	F	G	H	I
1	S.No.	Group Name	Check-in	Check-out	Nights	Double	Triple	Quad	Total
2	1	Babar 1	25-Mar	29-Mar	4	2	3	4	9
3	2	Babar 2	25-Mar	30-Mar	5	2	3	4	9
4	3	Babar 3	25-Mar	29-Mar	4	2	3	4	9
Data Sheet

Cell Formulas
Range	Formula
E2:E4	E2	=D2-C2
I2:I4	I2	=H2+F2+G2

Room Chart-updatedrooms.xlsx
	A	B	C	D
1	Date	Double	Triple	Quad
2	25-Mar	2	0
3	26-Mar
4	27-Mar
5	28-Mar
6	29-Mar
7	30-Mar
8	31-Mar
9	1-Apr
10	2-Apr
11	3-Apr
12	4-Apr
13	5-Apr
14	6-Apr
15	7-Apr
16	8-Apr
Availablity Sheet

Cell Formulas
Range	Formula
B2	B2	=IF(AND(A2>='Data Sheet'!C2,A$2<='Data Sheet'!D2-1),'Data Sheet'!F2,0)
C2	C2	=IF(AND(A2>='Data Sheet'!D2,A$2<='Data Sheet'!E2-1),'Data Sheet'!G2,0)+IF(AND(B2>='Data Sheet'!D2,B$2<='Data Sheet'!E2-1),'Data Sheet'!G2,0)

if was using this formula, but found it not useful by applying all the cells and using two many columns.

=IF(AND(A2>='Data Sheet'!C2,A$2<='Data Sheet'!D2-1),'Data Sheet'!F2,0)

is there any way using VBA or formula to solve this issue?

 

[jtakw]

the last thing, if I don't want to count the checkout date, is there any solution for that?

mean check-in 25th checkout 30th for 2 double rooms

the count should show from 25th till 29th, please advise? and many thanks in advance

@jtakw

Just take out the = after D2:D4, use this instead:

=SUMPRODUCT(('Data Sheet'!$C$2:$C$4<=$A2)*('Data Sheet'!$D$2:$D$4>$A2)*('Data Sheet'!$F$1:$H$1=B$1)*'Data Sheet'!$F$2:$H$4)

 

[babar1988]

Just take out the = after D2:D4, use this instead:

=SUMPRODUCT(('Data Sheet'!$C$2:$C$4<=$A2)*('Data Sheet'!$D$2:$D$4>$A2)*('Data Sheet'!$F$1:$H$1=B$1)*'Data Sheet'!$F$2:$H$4)

Thank you soo much

 

[jtakw]

You're welcome, thanks for the feedback.