Calculation of Multiple Outputs Using SUMPRODUCT in Google Sheets

Other Questions Google Sheets
1

I am developing a calendar tool for hotel bookings where I need to compute the average nightly rate for reservations that occur between two specified search dates. Here are some important factors to consider:

  • Bookings are entered with a check-in date and a check-out date.
  • Each booking has a single price listed in the same row.
  • The check-out date is counted as available since the room will be empty by the daily check-in time.
  • I need to focus only on the booking values for the days that fall within the two search dates.
  • It’s important to note that bookings may go beyond the first and second search dates, but I will disregard their values for dates outside the specified range.
  • I prefer to achieve this using formulas instead of coding.

I’ve been experimenting with a SUMPRODUCT formula for most of the day, and I’m close to achieving my goal; however, my current approach only provides the total count of occupied rooms during the specified period. Here’s the formula I have:

=ARRAYFORMULA(SUMPRODUCT(GTE(EndDate_Bookings, StartDate_Report+1),  LTE(StartDate_Bookings, EndDate_Report+0),IF((StartDate_Bookings<=StartDate_Report)*(EndDate_Bookings>=EndDate_Report),((EndDate_Report-StartDate_Report+1)),IF(StartDate_Bookings<StartDate_Report,EndDate_Bookings-StartDate_Report,IF(EndDate_Bookings>EndDate_Report,(EndDate_Report+1)-StartDate_Bookings,0)))

I suspect the issue lies in the array formula only producing a result for the first IF() condition, which seems incorrect. For clarity, here is an overview of the named ranges:

  • StartDate_Report / EndDate_Report = the two search dates.
  • StartDate_Bookings / EndDate_Bookings = columns for check-in and check-out dates.

The first IF() identifies all bookings overlapping with the search dates, the second checks for bookings that finish before the first search date, and the third looks for bookings that start after the second search date. Working with Google Sheets feels complex compared to Excel for me, so any advice would be greatly appreciated!

2

I’ve worked on a similar problem before, and a potential solution could involve breaking down the formula to look at overlapping days between the bookings and the search dates. You might want to try a helper column that calculates the actual nights occupied within the search period rather than calculating it inside your existing SUMPRODUCT formula. You can use a formula in this helper column to calculate the minimum of (EndDate_Report, EndDate_Bookings) less the maximum of (StartDate_Report, StartDate_Bookings), and ensure it doesn’t fall below zero - this will give you the actual nights within the search period. Once you have that, you can use SUMPRODUCT to multiply these nights by their respective prices and then average these values.

3

I totally get how challenging it can be to try to work all of that into one formula. What worked for me in a similar situation was to separate the logic into different functioning parts so it becomes easier to troubleshoot and modify. You can first create a column that calculates whether each booking falls within the desired dates range. Then, create another column that calculates the effective duration within that range for every booking. Once you have that, you can use SUMPRODUCT to take those nights and multiply them by the rate, and then divide by the count of those valid nights. This approach keeps the formula straightforward, ensuring accurate calculations without missing out on any overlaps or unnecessary days.