How do I omit hidden data from autosum calculations?

Hi

Use SUBTOTAL() to calculate the sum (At least it works when rows are hidded
using autofilter - I'm not sure does it work when rows are simply hidden).


Arvi Laanemets


"wolfthrone" wrote in message
...
How do I omit hidden data from autosum calculations?

If the data is hidden by virtue of a filtering operation, rather than simply
hiding the rows, you can substitute

=SUBTOTAL(9,range)

for

=SUM(range)

The SUBTOTAL() fucntion will ignore rows that the filter hides

"wolfthrone" wrote:

How do I omit hidden data from autosum calculations?

I am using 2000 so can't test this, but starting with 2003 (I believe) you
can also filter hidden rows not hidden by filtering by adding to whatever
number you are using in the subtotal formula. i.e.
=SUBTOTAL(9,range)
would become
=SUBTOTAL(109,range)

--
Kevin Vaughn


"Duke Carey" wrote:

If the data is hidden by virtue of a filtering operation, rather than simply
hiding the rows, you can substitute

=SUBTOTAL(9,range)

for

=SUM(range)

The SUBTOTAL() fucntion will ignore rows that the filter hides

"wolfthrone" wrote:

How do I omit hidden data from autosum calculations?

Hi Arvin

For your information, in XL2003 you can use sub-total with any hidden
rows, as well as those hidden with filter.
You just use 100 added to the normal Subtotal parameter e.g

=SUBTOTAL(109,A5:A1000) as opposed to =SUBTOTAL(9,A5:A1000)

--
Regards

Roger Govier


"Arvi Laanemets" wrote in message
...
Hi

Use SUBTOTAL() to calculate the sum (At least it works when rows are
hidded
using autofilter - I'm not sure does it work when rows are simply
hidden).


Arvi Laanemets


"wolfthrone" wrote in message
...
How do I omit hidden data from autosum calculations?

:-)

Btw., as I use Xl2000, all additional features of any versions of Xl200* are
beyound my experience.


Arvi Laanemets


"Roger Govier" wrote in message
...
Sorry Arvi

An extraneous "n" crept onto the end of your name. Can't even blame "fat
fingers" for that one<bg
Maybe it was autocorrect and I didn't notice.

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi Arvin

For your information, in XL2003 you can use sub-total with any hidden
rows, as well as those hidden with filter.
You just use 100 added to the normal Subtotal parameter e.g

=SUBTOTAL(109,A5:A1000) as opposed to =SUBTOTAL(9,A5:A1000)

--
Regards

Roger Govier


"Arvi Laanemets" wrote in message
...
Hi

Use SUBTOTAL() to calculate the sum (At least it works when rows are
hidded
using autofilter - I'm not sure does it work when rows are simply
hidden).


Arvi Laanemets


"wolfthrone" wrote in message
...
How do I omit hidden data from autosum calculations?