How do I omit hidden data from autosum calculations?

A

Arvi Laanemets

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
 
D

Duke Carey

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
 
K

Kevin Vaughn

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)
 
R

Roger Govier

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)
 
R

Roger Govier

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.
 
A

Arvi Laanemets

:)

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


Arvi Laanemets
 
Top