Excel 2000 - subtotals in filters

[CATRAINOR]

I have a large worksheet showing projected costs over a range of years for several hundred buildings. The full worksheet shows subtotals on a row by row and column by column basis. However I want to have a facility like the Autofilter function to zoom in on specific ranges that will show the subtotals for those selected cells only. The worksheet will be sent to a client so they can filter the data as they require do I cannot write fixed formulae in advance as I will not know which years or ranges of buildings they would like to look at. The data must remain in Excel, importing to Access is not an option.

Autofilter is the ideal filter for user convenience but it does not produce the row/column subtotals.

 

[TonyJollans]

Hi CATRAINOR,

Subtotals work well with filters; they only include visible data when there ia a filter on their range, so you should be just fine. Can you give an example of something not producing the results you want.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[CATRAINOR]

I will not pretend to be an expert at subtotals but- I have no control over the order in which the client will sort the data!

I must assume that the data will be subject to a number of different sorts and then be subject to an Autofilter!

A very simplified example of my problem is shown below.
Col A Col B Col C Col D Col E
Row 1 High St 500 750 250 1500
Row 2 Low St 300 600 100 1000
Row 3 Mid St 1000 250 750 2000
Row 4 Low St 100 1200 800 2100
Row 5 1900 2800 1900 6600

Row 5 and Col E being subtotals and E5 being a Grand Total

If I use an Autofilter on columns A - D and Rows 1-4 I can show (for example) data for Low St only but the totals (Columns E and Row 5) still show the values for all the data. If I include Cols E and Row 5 in the Autofilter selection they are excluded altogether of course. I appreciate sorting first by Col A and applying subtotals will do the trick but I am not in a position to be able to do any form of sort first as I cannot predict what the client will want to filter on at any perticular time. Ideas?

 

[xlbo]

You need to use the SUBTOTAL function rather than the SUM function to create your SUMs then - the SUBTOTAL function will sum / average / count only the visible rows which have not been filtered out

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[CATRAINOR]

Thanks - the SUBTOTAL function is just what I need, pity the Excel Help function buries the bit about it excluding filtered out data so deep that I never would have found it otherwise. Thanks to you both!