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

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

 

[Ken Wright]

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.