The SUBTOTAL function can easily generate sums and counts for hidden and non-hidden rows. However, it isn't able to handle criteria like COUNTIF or SUMIF without some help. One solution is to use SUMPRODUCT to apply both the SUBTOTAL function (via OFFSET) and the criteria. The details of this approach are described below.
How this formula works
At the core, this formula works by setting up two arrays inside SUMPRODUCT. The first array applies criteria, and the second array handles visibility:
Where TRUE means "meets criteria". Note because we are using multiplication (*) on this array, the TRUE FALSE values will automatically be converted to 1's and 0's by the math operation, so we end up with:
SUBTOTAL is able to exclude hidden rows when running 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 of results 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 with an expression built on the ROW function:
The criteria and visibility arrays work the same as explained above, excluding cells that are not visible. If you need partial matching, you can construct an expression using ISNUMBER + SEARCH, as explained here.
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...
The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. This sounds boring, but SUMPRODUCT is an incredibly versatile function that can be used to count and sum like COUNTIFS or SUMIFS, but with more...
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.