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?
--
Boris

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)

--
Regards

Roger Govier


"BorisS" wrote in message
...
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?
--
Boris

Boris

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

as opposed to:

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

Regards

Trevor


"BorisS" wrote in message
...
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?
--
Boris

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


"BorisS" wrote:

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?
--
Boris

I just use:
=SUBTOTAL(2,<range)

"BorisS" wrote:

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?
--
Boris

I thought that SUBTOTAL(2, ...) did a COUNT, not a SUM?
--
David Biddulph

"Eric" wrote in message
...
I just use:
=SUBTOTAL(2,<range)

"BorisS" wrote:

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?
--
Boris

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

"Trevor Shuttleworth" wrote in message
...
Boris

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

as opposed to:

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

Regards

Trevor

"BorisS" wrote in message
...
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?
--
Boris

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


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Both (9, ... and (109, ... ignore rows hidden by the filter. The
difference is in the treatment of manually hidden rows.
--
David Biddulph

"Trevor Shuttleworth" wrote in message
...
Boris

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

as opposed to:

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

Regards

Trevor

"BorisS" wrote in message
...
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?
--
Boris