Nightly hotel rate calculation

The SUMPRODUCT function multiplies arrays together and returns the sum of products. The trick is to use simple array expressions to "cancel out" the irrelevant rates in the table. SUMPRODUCT then simply sums the rates that remain.

Working from the inside out, this formula uses boolean logic to "filter" the rate data. The filter is constructed with three expressions, provided as the first argument to SUMPRODUCT:

(dates>=J5)* // date greater than or equal to Dec 3
(dates<J6)* // date less than Dec 7
(rooms=J4)* // room is "King"

Each of these expressions returns an array of TRUE FALSE values:

{FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}*
{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}*
{FALSE,FALSE,TRUE,FALSE,FALSE}

The math operation of multiplying the arrays together coerces the TRUE FALSE values to 1s and 0s, so we can visualize the operation more simply like this:

{0;0;1;1;1;1;1;1;1;1;1}*
{1;1;1;1;1;1;0;0;0;0;0}*
{0,0,1,0,0}

The result is a two-dimensional array like this:

{0,0,0,0,0;0,0,0,0,0;0,0,1,0,0;0,0,1,0,0;0,0,1,0,0;0,0,1,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0}

This is the array that does the filtering in this example. If we overlay the filter array on the rate area of the worksheet, it's easier to understand what's happening:

Notice the array only contains 1s where we want the rate information to come through. Every other value is zero. When this array is multiplied by the rate data (see below) the zeros will cancel out rate data that is not relevant to room, check-in, and check-out information.

The second argument provided to SUMPRODUCT contains all room rates, in the named range rates (C5:G15). This also is a two-dimensional array:

{65,80,90,105,175;80,95,105,120,250;80,95,105,120,250;80,95,105,120,250;90,105,125,150,250;100,115,125,150,300;100,115,125,150,300;65,80,90,105,300;80,95,105,120,250;80,95,105,120,250;80,95,105,120,250}

Inside SUMPRODUCT, the filtering array described above is multiplied by the named range rates. Both arrays are the same size, the result is an array of the same dimensions. Notice only the rates associated with the stay (as defined by check-in, check-out, and room) have survived the filter:

{0,0,0,0,0;0,0,0,0,0;0,0,105,0,0;0,0,105,0,0;0,0,125,0,0;0,0,125,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0}

Finally, SUMPRODUCT sums all values in the array and returns a final result, 460.

Note: although the rate information is organized with vertical dates and horizontal rooms, it can be transposed and the formula will still function correctly.

With the FILTER function

You can also solve this problem nicely with the new FILTER function:

=SUM(FILTER(INDEX(rates,0,MATCH(J4,rooms,0)),(dates>=J5)*(dates<J6)))

The gist of the solution is that we use the INDEX function to extract just the rates for a King room, and then we feed those rates to FILTER, which extracts just the rates for relevant dates. FILTER delivers these rates to the SUM function, which returns a final result.