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]

Hi, had a busy day and been trying to catch up with the forum stuff in between.

Could you confirm if the result that I mentioned is correct please, or correct it if I'm looking at it wrong?

Looking at row 2 in the bigger sheet, should that count as 20 room nights for January (4 rooms for 5 days)?

 

[Andrea1984]

Hi, had a busy day and been trying to catch up with the forum stuff in between.

Could you confirm if the result that I mentioned is correct please, or correct it if I'm looking at it wrong?

Hi Jason no the Reservation on row 2 is a 1 room that is staying 4 nights in January .
The Total stay is from the 31 December and check out on the 5January so 4 nights are in January

 

[jasonb75]

Hi, just wanted to let you know that I haven't forgotten this. I've had a few busy days, have been looking in between things but not getting the result of 499 that you are expecting.

From the way that you explained it I'm guessing that 31 Dec to 1 Jan is 1 night in Jan, 0 in Dec?

Working on that logic I'm getting a result of 479 as shown below.

JasonData File .xlsx
	A	B
2	01/01/2022
3	31/01/2022
4	RM	OTA_PROMO
5	STANDARD	137
6	DELUXE	226
7	J_SUITE	35
8	CORNERSTUDIO	31
9	M_SUITE	50
10	Total	479
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-1),"0000000"),"0;\0;\0"),--(Data!$B$2:$B$2257="Guaranteed"),--(Data!$H$2:$H$2257="OTA_PROMO"),--(Data!$J$2:$J$2257=A5))
B10	B10	=SUM(B5:B9)

 

[shinigamilight]

Tell me if this is what you wanted or something else

Easyupload.io - Upload Files and Share Them Easily

Uplaod files easily and share them with your friends and colleagues. File upload solution for everyone. Upload big files and get a link to share effortlesly.

easyupload.io

 

[Andrea1984]

Hi, just wanted to let you know that I haven't forgotten this. I've had a few busy days, have been looking in between things but not getting the result of 499 that you are expecting.

From the way that you explained it I'm guessing that 31 Dec to 1 Jan is 1 night in Jan, 0 in Dec?

Working on that logic I'm getting a result of 479 as shown below.

JasonData File .xlsx
	A	B
2	01/01/2022
3	31/01/2022
4	RM	OTA_PROMO
5	STANDARD	137
6	DELUXE	226
7	J_SUITE	35
8	CORNERSTUDIO	31
9	M_SUITE	50
10	Total	479
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-1),"0000000"),"0;\0;\0"),--(Data!$B$2:$B$2257="Guaranteed"),--(Data!$H$2:$H$2257="OTA_PROMO"),--(Data!$J$2:$J$2257=A5))
B10	B10	=SUM(B5:B9)

Dec to 1 Jan is 1 night in Jan, 0 in Dec? Hi jason its correct December is 0 and January is 1

 

[Andrea1984]

Tell me if this is what you wanted or something else

Easyupload.io - Upload Files and Share Them Easily

Uplaod files easily and share them with your friends and colleagues. File upload solution for everyone. Upload big files and get a link to share effortlesly.

easyupload.io

Hi thank you unforunately no since the Total Should be 499 the problem we are having is making excel understand that if there is a booking that come on the 31th December and leaves on the 2 Janaury . The breakdown should be 1 night for December and 1 night in Janaury

 

[jasonb75]

I think that I have the correct result.

Should row 491 be included in the results as 3 days? If it should then should it be counted as Deluxe or as M_Suite? Having 2 things in a single cell is a bad way to orgainise your data. As you have discovered, it will cause many problems with formulas.

As with last time, I'll need clarification of the point that I have raised above. I've managed to get the result of 499 if I include row 491, but so far, that is only with formulas that will not work in Excel 2019.

If you have the expected results by room type that make up the 499 expected total then that would be very helpful in getting the formula right as well.

 

[Andrea1984]

I think that I have the correct result.

Should row 491 be included in the results as 3 days? If it should then should it be counted as Deluxe or as M_Suite? Having 2 things in a single cell is a bad way to orgainise your data. As you have discovered, it will cause many problems with formulas.

As with last time, I'll need clarification of the point that I have raised above. I've managed to get the result of 499 if I include row 491, but so far, that is only with formulas that will not work in Excel 2019.

If you have the expected results by room type that make up the 499 expected total then that would be very helpful in getting the formula right as well.

Hi Jason Apologies for the delay ,
I did not receive the notification. The correct total is 499 . In regards to row 491 is should be Deluxe . I agree about the data unfortunately this is data that comes out from a property management system that does not pull it out clear .
Thank you in advance and apologies for the delay

 

[jasonb75]

Sorry, I should have asked another question earlier.

How would you know that it should be Deluxe and not M_Suite?

Similarly, what about any other similar entries where there are 2 (or more) room types in the same cell? Is there a specific order or reason for the one or shouldxbe counted as?

Once this is clarified it should be fairly simple to get it into a formula that gives you the correct results.

 

[Andrea1984]

Sorry, I should have asked another question earlier.

How would you know that it should be Deluxe and not M_Suite?

Similarly, what about any other similar entries where there are 2 (or more) room types in the same cell? Is there a specific order or reason for the one or shouldxbe counted as?

Once this is clarified it should be fairly simple to get it into a formula that gives you the correct results.

Hi Jason I know since I see it from the property management system . The row 491 should be 2 deluxe and 2 megaro suites
The system pulled the data wrong