Total on filter

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

On Thu, 6 Jul 2006 19:10:02 -0700, jk <jk@discussions.microsoft.com> wrote:

>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?

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

Gord Dibben wrote:
> 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
>
> On Thu, 6 Jul 2006 19:10:02 -0700, jk <jk@discussions.microsoft.com> wrote:
>
> >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?

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

"Gord Dibben" wrote:

> 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
>
> On Thu, 6 Jul 2006 19:10:02 -0700, jk <jk@discussions.microsoft.com> wrote:
>
> >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?
>
>

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

On Fri, 7 Jul 2006 04:48:01 -0700, jk <jk@discussions.microsoft.com> wrote:

>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
>
>"Gord Dibben" wrote:
>
>> 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
>>
>> On Thu, 6 Jul 2006 19:10:02 -0700, jk <jk@discussions.microsoft.com> wrote:
>>
>> >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?
>>
>>