Debra Dalgleish wrote:
Laurent Longre created a formula that lets you work with visible rows
after a filter. For information see, Power Formula Technique in this
article at John Walkenbach's web site:
http://j-walk.com/ss/excel/eee/eee001.txt

For example, to sum cells in column E, where column D contain the value
"Pass", after a filter on another column, you could use the
following, where there are no blank cells in those rows in column A:

=SUMPRODUCT((D2:D200="Pass")*(E2:E200)*(SUBTOTAL(3 ,OFFSET(A2:A200,ROW(A2:A200)-MIN(ROW(A2:A200)),,1))))

[...]

Can be a bit shorter and faster:

=SUMPRODUCT(--(D2:D200="Pass"),(SUBTOTAL(9,OFFSET(E2,ROW(E2:E200 )-ROW(E2),,1))))

Caveat. This shortened up version skips the range to which the filter is
applied. It will thus not take into account the empty cells in the
skipped range, while the formula you suggest would if A2:A200 is the
range the filter is applied.