calculating filtered results

B

BorisS

I believe that before '07, there was some way I could have a sum formula (or
some derivation of it) that only summed the visible elements of a filtered
list. Any help on what the method is for that?
 
R

Roger Govier

Hi

Try
=SUBTOTAL(9,A1:A100)
for XL2002 and earlier to sum only visible rows from a filtered list.

In XL2003 there was the addition of any rows that were visible, whether
the filter was a result of an Autofilter, or manually hiding rows. That
uses
=SUBTOTAL(109,A1:A100)
 
T

Trevor Shuttleworth

Boris

=SUBTOTAL(109,B4:B9) totals visible rows

as opposed to:

=SUBTOTAL(9,B4:B9) totals all rows

Regards

Trevor
 
E

Elkar

Look up help on the SUBTOTAL function. It can be used to include/exclude
filtered values in a variety of calculations (sum, count, average, etc...).
It works great in Excel 2003, but I believe its uses may have been more
limited in earlier versions.

HTH,
Elkar
 
D

David Biddulph

Both (9, ... and (109, ... ignore rows hidden by the filter. The difference
is in the treatment of manually hidden rows.
 
T

Trevor Shuttleworth

Absolutely right ... I knew what I meant ;-)


David Biddulph said:
Both (9, ... and (109, ... ignore rows hidden by the filter. The
difference is in the treatment of manually hidden rows.
Click to expand...
 
Top