where dates (B5:B15), rooms (C4:G4), and rates (C5:G15) are named ranges. With check-in on December 3 and check-out on December 7 (4 nights) in a King room, the formula returns a total of $460. 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: Each of these expressions returns an array of TRUE FALSE values: 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: The result is a two-dimensional array like this: 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: 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 the stay (as defined by check-in, check-out, and room) have survived the filter: 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: The gist of the solution is that we use the INDEX function to extract just the rates for a King room, then we feed those rates to FILTER, which extracts just the rates for relevant dates. FILTER deliverers these rates to the SUM function, which returns a final result.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
