Excel 2002 - subtotal function only calculates visible cells in an auto filtered range. Why ?

[tur13o]

--------------
Is it only me then that thinks this feature is mad ?



Ah well.



As it happens I found out the if you hide your detailed data using the
out-line feature instead of the auto-filter, then you "can" have your cake
and eat it ( Ken ) ! Hiding the detail using the out-line doe not make
subtotal evaluate only visible data. Cool !

--------------
All messages from thread
Message 1 in thread
From: tur13o ([email protected])
Subject: Excel 2002 - subtotal function only calculates visible cells
in an auto filtered range. Why ?


View this article only
Newsgroups: microsoft.public.excel
Date: 2004-02-12 06:26:56 PST


Is there a way to make =subtotal(9,range) regard all cells in the range it
refers to whether they are visible or not ?



I have been searching the dejanews archives on this one and there seems to
be plenty of posts asking how to do calculations on only the visible cells
in a filtered range - and there are wizzie formulas to do it suggested.



But I have found that subtotal does this as standard in Excel 2002. Only
problem is I don't want it to!



I want the subtotal figures to be the subtotal of the referenced range of
cells- regardless of whether they are hidden or visible.



Presently I have auto calc switched off, so that when the range is filtered
the subtotals don't change. But this is no good for others who use the
sheet.



I don't really want to change to sum( ) and lose the out-lining that you get
with subtotal.Post a follow-up to this message

Message 2 in thread
From: Ken Wright ([email protected])
Subject: Re: Excel 2002 - subtotal function only calculates visible
cells in an auto filtered range. Why ?


View this article only
Newsgroups: microsoft.public.excel
Date: 2004-02-12 07:03:25 PST


Talk about wanting your cake and eating it????

However, with the word SUM in say A1, then in whatever cell you are using
for
your totals:-

=IF(A1="SUM",SUM(D10100),IF(A1="SUBTOTAL",SUBTOTAL(9,D10100),"OOPS!!!"))

You could dispense with the second If really, but Belt and Braces ensures
that
you don't get any rogue entries in A1.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Is there a way to make =subtotal(9,range) regard all cells in the range it
refers to whether they are visible or not ?



I have been searching the dejanews archives on this one and there seems to
be plenty of posts asking how to do calculations on only the visible cells
in a filtered range - and there are wizzie formulas to do it suggested.



But I have found that subtotal does this as standard in Excel 2002. Only
problem is I don't want it to!



I want the subtotal figures to be the subtotal of the referenced range of
cells- regardless of whether they are hidden or visible.



Presently I have auto calc switched off, so that when the range is filtered
the subtotals don't change. But this is no good for others who use the
sheet.



I don't really want to change to sum( ) and lose the out-lining that you get
with subtotal.

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004Post a
follow-up to this message




©2004 Google