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

  • Thread starter Thread starter tur13o
  • Start date Start date
T

tur13o

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.
 
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(D10:D100),IF(A1="SUBTOTAL",SUBTOTAL(9,D10:D100),"OOPS!!!"))

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