Excel 97 - Sum filtered list with creiteria

  • Thread starter Thread starter Ben
  • Start date Start date
B

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
 
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((D2:D200="Pencils")*(E2:E200)*(SUBTOTAL(3,OFFSET(A2:A200,ROW(A2:A200)-MIN(ROW(A2:A200)),,1))))
 
Back
Top