Conditional Subtotal

[James]

I would like to be able to add the values of one column
based on values in three other columns (no problem with
that), but would also like to be able to do this in a
SUBTOTAL type of manner, so that the total would reflect
the effects of filtering. That's the part I haven't been
able to figure out. I doesn't seem that SUBTOTAL is a
valid function in an array formula. Any help would be
appreciated. Thanks,

James.

 

[Debra Dalgleish]

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 count blank cells in column D, after a filter on another
column, you could use the following, where there are no blank cells in
column A:

=SUMPRODUCT((D2200="")*(SUBTOTAL(3,OFFSET(A2:A200,ROW(A2:A200)-MIN(ROW(A2:A200)),,1))))