MrExcel Publishing
Your One Stop for Excel Tips & Solutions

subtotal malfunction

Posted by Mark on April 30, 2001 2:50 PM

I have a large table of rows that have been subtotalled with the data / subtotal feature. Suddenly in tier 2 mode, they are reporting zero. In tier 3 (expanded mode) they report normal subtotals of positive values. The formula in the subtotals isn't changing between shrunken and exploded.

example: =SUBTOTAL(9,E36:E46)

Did I mess with the options of this spreadsheet? Calculation is set to automatic and a manual F9 won't change the zero results of my shrunken table...
Hope you can help!

Posted by Dave Hawley on April 30, 2001 2:54 PM

Hi Mark

Are you aware that the SUBTOTAL function only includes visible cells in it's calculation.


OzGrid Business Applications

Posted by Mark on April 30, 2001 2:57 PM

Even in a data / subtotal environment?
It WAS working at one point, and I have to confess that if what you say is true, than the subtotal feature is useless...
Are you answering from the context of using the formula, as opposed to applying subtotals with the feature?

Posted by Mark on April 30, 2001 3:22 PM

Okay - I figured out what was happening - a remnant autofilter selection was tying up the totals - an odd result, but your answer was helpful - If you consider that "visible cells" are cells actually hidden, not "hidden" by an outline. I have noticed that custom views don't remember the outline tier that you are in, either, so those cells aren't "hidden" like other hiding features like autofilter or just hiding a row.
Thanks again for your help - very prompt.