Sum the Total room nights

Andrea1984

New Member
Joined
Nov 15, 2022
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
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)
 

Attachments

  • dataexcel.PNG
    dataexcel.PNG
    134.1 KB · Views: 20
  • Monthly.PNG
    Monthly.PNG
    7 KB · Views: 20

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Welcome to Mr Excel :)

To count everything that starts and / or ends in the specified month you need to look for stays that start before the end of the month and end after the beginning of the month.
As far as I can see (without testing the formula) the only thing that you need to do is change the first criteria range to column E as I have done below.
Excel Formula:
=SUMIFS(Data!$C1:$C375,Data!$E1:$E375,">="&$A$2,Data!$D1:$D375,"<="&$A$3,Data!$B1:$B375,"Guaranteed",Data!$H1:$H375,"OTA_PROMO",Data!$J1:$J375,A5)

You can't attach a workbook to the forum, but there is a tool called XL2BB that you can use to post your data directly from excel. There is a link to the tool in my signature block at the bottom of this post.
 
Upvote 0
Hi
I used the formula you provided =SUMIFS(Data!$C1:$C375,Data!$E1:$E375,">="&$A$2,Data!$D1:$D375,"<="&$A$3,Data!$B1:$B375,"Guaranteed",Data!$H1:$H375,"OTA_PROMO",Data!$J1:$J375,A5)
and when i tried it it gave me the Total of 496 which i was short of 3 room Correct figure is 499 .
The problem now is that when i change the data set ( before it was only from January to Feb) and now i have put the data ( January to March ) and used the Formula it gives me a Total of 503 room nights for Janaury .

I tried L2BB but it does not work . Thank you in advance
 

Attachments

  • dat2.PNG
    dat2.PNG
    132.4 KB · Views: 5
  • Monthly2.PNG
    Monthly2.PNG
    18.1 KB · Views: 4
Upvote 0
Welcome to Mr Excel :)

To count everything that starts and / or ends in the specified month you need to look for stays that start before the end of the month and end after the beginning of the month.
As far as I can see (without testing the formula) the only thing that you need to do is change the first criteria range to column E as I have done below.
Excel Formula:
=SUMIFS(Data!$C1:$C375,Data!$E1:$E375,">="&$A$2,Data!$D1:$D375,"<="&$A$3,Data!$B1:$B375,"Guaranteed",Data!$H1:$H375,"OTA_PROMO",Data!$J1:$J375,A5)

You can't attach a workbook to the forum, but there is a tool called XL2BB that you can use to post your data directly from excel. There is a link to the tool in my signature block at the bottom of this post.
Hi
I used the formula you provided =SUMIFS(Data!$C1:$C375,Data!$E1:$E375,">="&$A$2,Data!$D1:$D375,"<="&$A$3,Data!$B1:$B375,"Guaranteed",Data!$H1:$H375,"OTA_PROMO",Data!$J1:$J375,A5)
and when i tried it it gave me the Total of 496 which i was short of 3 room Correct figure is 499 .
The problem now is that when i change the data set ( before it was only from January to Feb) and now i have put the data ( January to March ) and used the Formula it gives me a Total of 503 room nights for Janaury .

I tried L2BB but it does not work . Thank you in advance

 

Attachments

  • dat2.PNG
    dat2.PNG
    132.4 KB · Views: 3
  • Monthly2.PNG
    Monthly2.PNG
    18.1 KB · Views: 3
Upvote 0
I think that I have have misunderstood what you asked for, could you clarify which of the following should be included in the January results please.

Start in December and End in January
Start in January and End in January
Start in January and End in February

The formula will include all of the above with the change that I made to it. Reading your question again I think that maybe you don't want to include stays that start in January and end in February?

If that is the case then you need to check both dates against column E,
Excel Formula:
=SUMIFS(Data!$C1:$C375,Data!$E1:$E375,">="&$A$2,Data!$E1:$E375,"<="&$A$3,Data!$B1:$B375,"Guaranteed",Data!$H1:$H375,"OTA_PROMO",Data!$J1:$J375,A5)
 
Upvote 0
I think that I have have misunderstood what you asked for, could you clarify which of the following should be included in the January results please.

Start in December and End in January
Start in January and End in January
Start in January and End in February

The formula will include all of the above with the change that I made to it. Reading your question again I think that maybe you don't want to include stays that start in January and end in February?

If that is the case then you need to check both dates against column E,
Excel Formula:
=SUMIFS(Data!$C1:$C375,Data!$E1:$E375,">="&$A$2,Data!$E1:$E375,"<="&$A$3,Data!$B1:$B375,"Guaranteed",Data!$H1:$H375,"OTA_PROMO",Data!$J1:$J375,A5)
Hi Jason
Basically i have the data set that will have data from 1st January 2022 till 31 March 2023
In the Monthly Tab i will need the Totals Stays from 1st of each month to the end of each Month .
The problem is that i may have reservations that arrive on the 30th December and leave on 2 January so that would mean that out of the 3 nights only 1 is for Janaury i also may have reservations that arrive 30th Janaury and leave on 02 Febraury and this would mean that that out of 4 Nights 2 go in the Monthly Totals of January .

thank you in advance really appreciate your support and time
 

Attachments

  • Monthly2.PNG
    Monthly2.PNG
    18.1 KB · Views: 4
  • dat2.PNG
    dat2.PNG
    132.4 KB · Views: 5
Upvote 0
So you need to count the difference in days rather than sum up the rooms in column C? I was looking at that and wondering after you mentioned 'room nights' in your last reply.

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

If that is what you need then this is going to be a lot more complicated than a sumifs formula. Once you have confirmed that what I've said above is correct (or corrected it if I'm wrong) I'll look at a formula that will do what you need but it may take me a couple of days to get it done now as I'll need to set up some data similar to yours to test on.

If you could upload your workbook to a file share site (something like dropbox) and post a link to the file in your reply that would be extremely helpful.
 
Upvote 0
So you need to count the difference in days rather than sum up the rooms in column C? I was looking at that and wondering after you mentioned 'room nights' in your last reply.

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

If that is what you need then this is going to be a lot more complicated than a sumifs formula. Once you have confirmed that what I've said above is correct (or corrected it if I'm wrong) I'll look at a formula that will do what you need but it may take me a couple of days to get it done now as I'll need to set up some data similar to yours to test on.

If you could upload your workbook to a file share site (something like dropbox) and post a link to the file in your reply that would be extremely helpful.
Hi Jason Please find attached the link of Dropbox

thank you in advance
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top