Count visible rows only with criteria

To count visible rows only with criteria, you can use a rather complex formula based on SUMPRODUCT, SUBTOTAL, and OFFSET. In the example shown, the formula in C12 is:

=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))

Preface

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:

=SUMPRODUCT(criteria*visibility)

The criteria is applied with part of the formula:

=(C5:C8=C10)

Which generates an array like this:

{FALSE;TRUE;FALSE;TRUE}

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:

{0;1;0;1}

The visibility filter is applied using SUBTOTAL, with function number 103.

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:

=ROW(C5:C8)-MIN(ROW(C5:C8)

which will generate an array like this:

{0;1;2;3}

In summary, the second array (which handles visibility using SUBTOTAL), is generated like this:

=SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))
=SUBTOTAL(103,OFFSET(C5,{0;1;2;3},0))
=SUBTOTAL(103,{"East";"West";"Midwest";"West"})
={1;0;1;1}

And, finally, we have:

=SUMPRODUCT({0,1,0,1}*{1;0;1;1})

Which returns 1.

Multiple criteria

You can extend the formula to handle multiple criteria like this:

=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))

Summing results

To return a sum of values instead of a count, you can adapt the formula to include a sum range:

=SUMPRODUCT(criteria*visibility*sumrange)

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.