If we take a look at the following formula...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),--(L2:L1
00="Open"))

....SUBTOTAL returns an array of 1's and 0's for the range of interest.
It returns 1 for all visible cells that are not empty, and returns 0 for
those that are hidden. Therefore...

SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1))

....evaluates to something like...

1
1
0
0
0
1
0

....and so on for the remaining cells in the range. The second
argument...

--(L2:L100="Open")

....evaluates to something like...

1
0
0
1
0
0
1

....and so on for the remaining cells in the range. Then, SUMPRODUCT
multiplies the evaluations and sums the result.

Hope this helps!

In article ,
"Scott buckwalter" wrote:

Thanks! It works! I'm interested in Why it works. Is there an explanation
somewhere? I understand the functions being used (mostly), but I dont see
how putting them together in this way makes this work. Thanks.