Calculating Total Cost for Rooming List with additional Hotel Matrix Sheet

I'm trying to calculate the 'Estimated Total Cost' for a traveller staying at a hotel. I have a rooming list sheet and a hotel matrix sheet. The rooming list has each traveller on each row and the '1' represents that they are staying that night.

The hotel matrix sheet has the room types, dates, nightly rates and total. However each night can change price which makes the calculation much more difficult to do

Can anyone assist how I could calculate the total cost per person staying at the hotel?

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Try this:

=SUMIFS({Nightly Rate}, {Room Type}, @cell = [Room Type]@row, {Dates}, AND(@cell >= [Start Date]@row, @cell < [End Date]@row))

All comments

Paul Newcome

Do you have columns for more than just those 3 days? Is it possible there would be multiple stays such as Days 1 - 3 and then again for Days 9 - 12?

justdan2

Hi Paul, there would be no break in stay dates, just consecutive.

Thanks.

Paul Newcome

Ok. Assuming you have a [Start Date] column that indicates the first day and an [End Date] column that indicates the last day, you could use something along the lines of

=SUMIFS({Nightly Rate}, {Room Type}, @cell = [Room Type]@row, {Dates}, AND(@cell >= [Start Date]@row, @cell <= [End Date]@row))

justdan2

Hi Paul,

Thanks so much for your help. It's very close but it's returning an extra night's cost at the end.

So if the check-in date column reads 5th April and check-out is 10th April it should sum 5 nights but it's including the night of the 10th April in the calculation as well, so 6.

Paul Newcome

Try this:

=SUMIFS({Nightly Rate}, {Room Type}, @cell = [Room Type]@row, {Dates}, AND(@cell >= [Start Date]@row, @cell < [End Date]@row))

justdan2

That works perfectly, thanks so much for your help !

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.