Explanation
In this example, the goal is to count visible rows where Region=“West”.
Row 13 meets this criteria, but has been hidden.
TheSUBTOTAL functioncan easily generatesumsandcountsfor visible rows.
However, SUBTOTAL is not able to apply criteria like theCOUNTIFS functionwithout help.
The details of this approach are described below.
Overview
At the core, this formula works by setting up twoarraysinside SUMPRODUCT.
The reason the expression is complex is that we need anarrayof results, not a single result.
This means we need to feed cells into SUBTOTAL one at a time using theOFFSET function.
Adding up results
Finally, we are ready to add up the results.
For this, we use theSUMPRODUCT function.
The criteria and visibility arrays work the same as explained above, excluding cells that are not visible.
OFFSET is handy in formulas that require a dynamic range.
SUMPRODUCT Function
The Excel SUMPRODUCT function multipliesrangesorarraystogether and returns the sum of products.
you’re free to use INDEX to retrieve individual values, or entire rows and columns.
The MATCH function is often used together with INDEX to provide row and column numbers….
