Autofilter not recalculating totals
 

Please could someone let me know which solution is best for totals
calculating whilst using AutoFilter. I have list of projects (Alive or Dead)
with a total cost for each and a main total at bottom. If I autofilter on
Alive only the total remains for both Alive and Dead projects. Please Help ?

Autofilter does not prevent all values in the range from being included in
the totals, as you found out. But here are a couple other options to
investigate:

1) Sort your data range by the Alive/Dead column and use DataSubTotals.
This will give you Subtotals for each group plus a grand total for all
records. This feature can also be turned on/off as needed.

2) Create formulas using DSUM functions to selectively add up the totals for
the Active & Inactive groups of records.

HTH |:)

"BJ Magill" wrote:

Please could someone let me know which solution is best for totals
calculating whilst using AutoFilter. I have list of projects (Alive or Dead)
with a total cost for each and a main total at bottom. If I autofilter on
Alive only the total remains for both Alive and Dead projects. Please Help ?

Use the SUBTOTAL function.

=SUBTOTAL(9,A1:A5000)

This function ignores the filtered items.


Gord Dibben Excel MVP

On Wed, 23 Mar 2005 09:29:04 -0800, BJ Magill <BJ
wrote:

Please could someone let me know which solution is best for totals
calculating whilst using AutoFilter. I have list of projects (Alive or Dead)
with a total cost for each and a main total at bottom. If I autofilter on
Alive only the total remains for both Alive and Dead projects. Please Help ?