Total on filter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a sheet which contains many accounts in one column and amounts paid
in another column,I really dont need the auto filter which places a filter on
every column.How do you make a filter for the account column and at the same
time have a total for the amounts paid column when the filter shows only
those accounts?
 
Use the SUBTOTAL function.

=SUBTOTAL(9,B:B) which includes filtered and hidden values.

Or in 2003 version

=SUBTOTAL(109,A:A) which ignores hidden values.


Gord Dibben MS Excel MVP
 
In addition, if you only want the filter to apply to one column (or
even just part of that column), highlight the column (or cells F4:F100
for example, if your headings are in row 4) and then do Data | Filter |
Autofilter.

Hope this helps.

Pete
 
Hi,

Could you give me an example since my totals are on the F column and when i
use the sumtotal function i receive a circular reference error???? I am using
excel 2002/sp3. I do not receive the total results in either filtered or
unfiltered mode.Is there something i am missing?

Thanks for the support
 
If you use the entire column reference in your formula you cannot have that
formula in the same column or you will get the circular reference error.

To subtotal a filtered column F enter this formula in G1

=SUBTOTAL(9,F:F)

If you want to have your formula in Column F enter it as

=SUBTOTAL(9,F1:F100) or somesuch range.


Gord Dibben MS Excel MVP
 
Back
Top