Explanation
TheSUMPRODUCT functionmultiplies 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 usesboolean logicto “filter” the rate data.
Every other value is zero.
Both arrays are the same size, the result is an array of the same dimensions.
FILTER delivers these rates to theSUM function, which returns a final result.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
you could use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
Often, MATCH is combined with the…
