# Create Formula For Rooming List

#### canard2

##### New member
Hello I have a rooming list and need to identify what will be the total cost per person depending on the hotel they stay in and dates. Here is the information:
I have 4 hotels each with a different price that we have pre-paid for the guest. If I add the name of the hotel to the row I would like it to generate the total cost of the room for that individual by automatically calculating it against the number of nights.
On top of that I need to add 24.20€ per room. We pre-pay the rooms, however some might extend their stay and this they will have to pay upon departure and this needs to be summed up in another column.

 Hotel 06/12 07/12 08/12 09/12 Pre paid Pay onsite Hotel A 1 1 1 1 Price for 07/12 + 08.12 + 24.20€ Price for 06/12+09/12 Hotel B 1 1 Price for 07/12 + 08.12 + 24.20€

Thanks

#### NBVC

##### Super Moderator
Staff member
Can you post a sample workbook instead showing more details... I.e. where the price comes from, and how do you know that 07/12 and 058/12 are prepaid and 06/12 and 09/12 are Pay onsite? And give more background if possible to help us understand better your request.

#### canard2

##### New member
Can you post a sample workbook instead showing more details... I.e. where the price comes from, and how do you know that 07/12 and 058/12 are prepaid and 06/12 and 09/12 are Pay onsite? And give more background if possible to help us understand better your request.

Hello - I attached the spreadsheet
In purple are the pay on-site dates and in blue the pre paid nights (+ I have to include the 24.20€ fee)
I basically want to put the name of the hotel in Column G and it adds up all costs per what the guest requested.
Let's say :
Hotel A - Hilton = 100€ per night
Hotel B - Ramada = 150€ per night
Hotel C - Fairmont = 200€ per night

Thanks

#### Attachments

• List for Test.xlsx
21.8 KB · Views: 106

#### NBVC

##### Super Moderator
Staff member
See attached.

I create a little table of Hotels vs. Nightly prices on the side in U1:V4.

In P3 entered formula:

=IFERROR(VLOOKUP(G3,\$U\$2:\$V\$4,2,0)*SUM(\$I3:\$N3)+24.2,"")

in Q3 entered formula:

=IFERROR(VLOOKUP(G3,\$U\$2:\$V\$4,2,0)*SUM(\$H3,\$O3),"")

and in R3:

=IF(G3="","",SUM(P3:Q3))

each copied down.

I entered some samples to show results.

Also note, I am not sure but I think your formula in H3, copied across to O3 and down should be:

=IF(AND(\$B3<=H\$2,\$D3>=H\$2),1,0)

in order to also include the date in row 2, as an end date.

#### Attachments

• List for Test.xlsx.XLS
21.8 KB · Views: 100

#### canard2

##### New member
See attached.

I create a little table of Hotels vs. Nightly prices on the side in U1:V4.

In P3 entered formula:

=IFERROR(VLOOKUP(G3,\$U\$2:\$V\$4,2,0)*SUM(\$I3:\$N3)+24.2,"")

in Q3 entered formula:

=IFERROR(VLOOKUP(G3,\$U\$2:\$V\$4,2,0)*SUM(\$H3,\$O3),"")

and in R3:

=IF(G3="","",SUM(P3:Q3))

each copied down.

I entered some samples to show results.

Also note, I am not sure but I think your formula in H3, copied across to O3 and down should be:

=IF(AND(\$B3<=H\$2,\$D3>=H\$2),1,0)

in order to also include the date in row 2, as an end date.

This is great. I works. Thank you! I am learning Excel.

Staff member

#### canard2

##### New member
Just a quick update about this formula =IF(AND(\$B3<=H\$2,\$D3>=H\$2),1,0) should not be \$D3>=H\$2 because it's a rooming list so I don't need it to take into account the departure date but the day before.

Another question about this rooming list: From 22/02 to 27/02 guest have to pay a 4 night minimum even if they only sleep 3 nights. Ex. Line 33 only have 3 nights (this is why it turns red), he is aware that he has to pay 4 nights. Can I add a formula already in the Column P Formula or do I need to create another formula in another column?

#### NBVC

##### Super Moderator
Staff member
Just a quick update about this formula =IF(AND(\$B3<=H\$2,\$D3>=H\$2),1,0) should not be \$D3>=H\$2 because it's a rooming list so I don't need it to take into account the departure date but the day before.
I wasn't sure, just wanted you to be aware....

Another question about this rooming list: From 22/02 to 27/02 guest have to pay a 4 night minimum even if they only sleep 3 nights. Ex. Line 33 only have 3 nights (this is why it turns red), he is aware that he has to pay 4 nights. Can I add a formula already in the Column P Formula or do I need to create another formula in another column?

What if the room is rented for 1 or 2 nights? Is the 4 night min still in effect?

#### Hercules1946

##### New member
See attached.

I create a little table of Hotels vs. Nightly prices on the side in U1:V4.

In P3 entered formula:

=IFERROR(VLOOKUP(G3,\$U\$2:\$V\$4,2,0)*SUM(\$I3:\$N3)+24.2,"")

in Q3 entered formula:

=IFERROR(VLOOKUP(G3,\$U\$2:\$V\$4,2,0)*SUM(\$H3,\$O3),"")

and in R3:

=IF(G3="","",SUM(P3:Q3))

each copied down.

I entered some samples to show results.

Also note, I am not sure but I think your formula in H3, copied across to O3 and down should be:

=IF(AND(\$B3<=H\$2,\$D3>=H\$2),1,0)

in order to also include the date in row 2, as an end date.

Hi
When I open the attachment it complains about thr .XLS extension and appears identical to the post #3 attachment, and yet others haven't had a problem. Tried opening as .xlsx and same problem ????

#### NBVC

##### Super Moderator
Staff member
I might have added the wrong attachment. :embarassed: Here is the right one....

also, if canard2 does require the 4 day min no matter the days stayed in that date range, then the P3 formula would become:

=IFERROR(VLOOKUP(G33,\$U\$2:\$V\$4,2,0)*MAX(4,SUM(\$I33:\$N33))+24.2,"")

copied down.

#### Attachments

• Copy of List for Test.xlsx
19 KB · Views: 557
Last edited:

#### canard2

##### New member
I might have added the wrong attachment. :embarassed: Here is the right one....

also, if canard2 does require the 4 day min no matter the days stayed in that date range, then the P3 formula would become:

=IFERROR(VLOOKUP(G33,\$U\$2:\$V\$4,2,0)*MAX(4,SUM(\$I33:\$N33))+24.2,"")

copied down.

Great. This helps. Thanks.

#### canard2

##### New member
HelloI have another request. For the same spreadsheet.
I have different hotels. What formula can I use to see how many rooms I have left for my rooming list?

Cheers

#### NBVC

##### Super Moderator
Staff member
I would like you to update the sample with some relevant data, and show expected results.

#### canard2

##### New member
I would like you to update the sample with some relevant data, and show expected results.

Hi I found the solution using SUMIFS. This is the solution I used (in words) = =Roomblock-(SUMIFS(Column P(which is my 1st night),Column O (attributed hotel, Name of hotel) and it worked.

I wanted to just write the name of hotel but was unable to create the formula when the hotel had 2 words. Does the formula only accept one word (ex; =\$AF\$2-(SUMIFS(S\$3:S\$66,\$O\$3:\$O\$66,"catalonia")) -> this works but it I do =\$AF\$2-(SUMIFS(S\$3:S\$66,\$O\$3:\$O\$66,"Hilton NYC")) It does not work (I tried underscore, no space etc)

#### NBVC

##### Super Moderator
Staff member
That should work as long as there is an exact match. Make sure that your "match" in column O doesn't have extra space at the end or in between words.