Excel 97 - Sum filtered list with creiteria

[Ben]

Hi

I am trying to sum a column that is filtered, but only sum those values
where an adjacent column is a particular value. Is this possible?

Thanks
Ben

 

[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 sum cells in column E, where column D contain the value
"Pencils", after a filter on another column, you could use the
following, where there are no blank cells in those rows in column A:

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