Sum the Total room nights

[Andrea1984]

Hello Everyone ,
Unfortuantely i cannot load the Excel sheet otherwise i would have since it would have been simple .
The Problem is the following:
In the Monthly TAB i would like to report the Total number of rooms for a specific room type Example ( Standard ) by a Specific Market Segement ( OTA_PROMO) .
I have tried the below formula in the monthly Tab however the challenge that i am having is that is not giving me the correct Total since some bookings come in December and leave in January and i would need the correct Total of rooms in January


=SUMIFS(Data!$C1:$C375,Data!$D1:$D375,">="&$A$2,Data!$D1:$D375,"<="&$A$3,Data!$B1:$B375,"Guaranteed",Data!$H1:$H375,"OTA_PROMO",Data!$J1:$J375,A5)

 

[jasonb75]

I know since I see it from the property management system

Unfortunately, a formula can not work based on what you know. We have to tell the formula what is correct.

When there are multiple room types in one cell, this formula will use the first one in the cell for the count, anything after that will be ignored. It gives the correct results for the example but it may not necessarily be accurate with a different data set.

JasonData File .xlsx
	A	B
1
2	01/01/2022
3	31/01/2022
4	RM	OTA_PROMO
5	STANDARD	147
6	DELUXE	233
7	J_SUITE	36
8	CORNERSTUDIO	33
9	M_SUITE	50
10	Total	499
Monthly

Cell Formulas
Range	Formula
B5:B9	B5	=SUMPRODUCT(TEXT(NETWORKDAYS.INTL(IF(Data!$D$2:$D$2257>=$A$2,Data!$D$2:$D$2257,$A$2),IF(Data!$E$2:$E$2257<=$A$3,Data!$E$2:$E$2257-1,$A$3),"0000000"),"0;\0;\0")*(Data!$B$2:$B$2257="Guaranteed")*(Data!$H$2:$H$2257="OTA_PROMO")*IFERROR((SEARCH(A5,Data!$J$2:$J$2257)=1),0))
B10	B10	=SUM(B5:B9)

 

[Andrea1984]

Unfortunately, a formula can not work based on what you know. We have to tell the formula what is correct.

When there are multiple room types in one cell, this formula will use the first one in the cell for the count, anything after that will be ignored. It gives the correct results for the example but it may not necessarily be accurate with a different data set.

JasonData File .xlsx
	A	B
1
2	01/01/2022
3	31/01/2022
4	RM	OTA_PROMO
5	STANDARD	147
6	DELUXE	233
7	J_SUITE	36
8	CORNERSTUDIO	33
9	M_SUITE	50
10	Total	499
Monthly

Cell Formulas
Range	Formula
B5:B9	B5	=SUMPRODUCT(TEXT(NETWORKDAYS.INTL(IF(Data!$D$2:$D$2257>=$A$2,Data!$D$2:$D$2257,$A$2),IF(Data!$E$2:$E$2257<=$A$3,Data!$E$2:$E$2257-1,$A$3),"0000000"),"0;\0;\0")*(Data!$B$2:$B$2257="Guaranteed")*(Data!$H$2:$H$2257="OTA_PROMO")*IFERROR((SEARCH(A5,Data!$J$2:$J$2257)=1),0))
B10	B10	=SUM(B5:B9)

Hi Jason
Apologies for the delay in coming back to you unfortuantely i contracted Covid .
Thank you for the Formula it works and really appreciated your help . Please could i ask you what i need to change on the Formula to calculate the revenue that is in the Coloum L
thank you

 

[jasonb75]

If we are to assume that the revenue in column L is for all nights and that you want to calculate it pro-rata for the chosen month / period then it will be

=SUMPRODUCT(IFERROR((TEXT(NETWORKDAYS.INTL(IF(Data!$D$2:$D$2257>=$A$2,Data!$D$2:$D$2257,$A$2),IF(Data!$E$2:$E$2257<=$A$3,Data!$E$2:$E$2257-1,$A$3),"0000000"),"0;\0;\0")*(Data!$B$2:$B$2257="Guaranteed")*(Data!$H$2:$H$2257="OTA_PROMO")*(SEARCH(A5,Data!$J$2:$J$2257)=1))/(Data!$E$2:$E$2257-Data!$D$2:$D$2257),0),Data!$L$2:$L$2257)

 

[Andrea1984]

If we are to assume that the revenue in column L is for all nights and that you want to calculate it pro-rata for the chosen month / period then it will be

=SUMPRODUCT(IFERROR((TEXT(NETWORKDAYS.INTL(IF(Data!$D$2:$D$2257>=$A$2,Data!$D$2:$D$2257,$A$2),IF(Data!$E$2:$E$2257<=$A$3,Data!$E$2:$E$2257-1,$A$3),"0000000"),"0;\0;\0")*(Data!$B$2:$B$2257="Guaranteed")*(Data!$H$2:$H$2257="OTA_PROMO")*(SEARCH(A5,Data!$J$2:$J$2257)=1))/(Data!$E$2:$E$2257-Data!$D$2:$D$2257),0),Data!$L$2:$L$2257)

thank you i tried it and it does not work , is there a missing part ?

 

[jasonb75]

It's all there, I get results from the test file that you provided for the original question (16091.50 for standard).

 

[Andrea1984]

If we are to assume that the revenue in column L is for all nights and that you want to calculate it pro-rata for the chosen month / period then it will be

=SUMPRODUCT(IFERROR((TEXT(NETWORKDAYS.INTL(IF(Data!$D$2:$D$2257>=$A$2,Data!$D$2:$D$2257,$A$2),IF(Data!$E$2:$E$2257<=$A$3,Data!$E$2:$E$2257-1,$A$3),"0000000"),"0;\0;\0")*(Data!$B$2:$B$2257="Guaranteed")*(Data!$H$2:$H$2257="OTA_PROMO")*(SEARCH(A5,Data!$J$2:$J$2257)=1))/(Data!$E$2:$E$2257-Data!$D$2:$D$2257),0),Data!$L$2:$L$2257)

It's all there, I get results from the test file that you provided for the original question (16091.50 for standard).

ok i will need to try it on the other Computer that i have at work since on my personal laptop i am having issues with excel

 

[jasonb75]

You might need to array confirm the formula by pressing Ctrl Shift Enter instead of just Enter (this will be necessary with excel 2019 or older).

 

[Andrea1984]

You might need to array confirm the formula by pressing Ctrl Shift Enter instead of just Enter (this will be necessary with excel 2019 or older).

Dear Jason
I wanted to thank you for your help i have sent the day creating the report that i needed and without your assistance it would have been a nightmare .
I had 2 questions.
1. how do i modify the formula to give me the totals of a OTA Promo without taking in consideration the Room types ?
2. The second question is how do i modify the formula that it gives me the Total of the OTA Promo per day of the week ?

I am new on the forum and since i reallly appreciate your help if you are based in London i wanted to send you a voucher for a meal in one of restaurants .