Where TRUE means "meets criteria". Note that because we are using multiplication (*) inside the first (and only) array given to SUMPRODUCT, the TRUE FALSE values will automatically be converted to:
The visibility filter is applied using SUBTOTAL.
SUBTOTAL is able to exclude hidden rows in a variety of calculations, so we can use it in this case to generate a "filter" to exclude hidden rows inside of SUMPRODUCT. The problem though is that SUBTOTAL returns a single number, while we need an array to use it successfully inside SUMPRODUCT.
The trick is to use OFFSET to feed SUBTOTAL one reference per row, so that OFFSET will return one result per row.
Of course, that requires another trick, which is to give OFFSET an array that contains one number per row, starting with zero. We do that using:
The Excel SUBTOTAL function returns an aggregate result for supplied values. SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, and others (see table below), and SUBTOTAL function can either include or exclude values in hidden rows.
The Excel OFFSET function returns a reference to a range constructed in parts: a starting point, a row and column offset, and a final height and width in rows and columns. OFFSET is handy in formulas that dynamically average or sum "last n values...
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.