Explanation
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.
Explanation
Important: This formula assumes that units are the last 2 characters of the text value that includes both a number and a unit of measure.
This formula works because digital units have a “power of 10” relationship. At the core, this formula separates the number part of the size from the unit, then divides the number by the appropriate divisor to normalize to Gigabytes. The divisor is calculated as a power of 10, so the formula reduces to this:
=number/10^power
To get the number, the formula extracts all characters from the left up to but not including the units:
LEFT(B5,LEN(B5)-2)
To get “power”, the formula matches the unit with a hard-coded array constant, {“PB”,“TB”,“GB”,“MB”,“KB”}:
MATCH(RIGHT(B5,2),{"PB","TB","GB","MB","KB"},0)
The result from MATCH is the position of the unit in the array constant. For example, for the formula in C5, the unit is “KB”, so the result is 5. This result is adjusted by subtracting 3, then multiplying the result by 3, which yields 6 as the power, which is used as the exponent to calculate the correct result in gigabytes:
=900/10^6
=900/1000000
=0.0009
Binary standard formula
Computers use the binary number system to store and report data size, but the prefixes like “kilo”, “mega”, “giga”, etc. are based on the metric system. It’s a confusing topic, but using decimal units for storage on a computer isn’t really correct, and the discrepancy increases as units get larger. The formula below will normalize to binary units.
=LEFT(A1,LEN(A1)-2)/2^((MATCH(RIGHT(A1,2),{"PB","TB","GB","MB","KB"},0)-3)*10)
With this formula, you are technically getting Gibibytes (GiB), not Gigabytes. More information here and here .
