Total on filter jk 07.07.2006 02:10:02 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?

Re: Total on filter Gord Dibben <gorddibbATshawDOTca> 07.07.2006 02:36:21 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[ at ]discussions.microsoft.com> wrote: [Quoted Text] >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?

Re: Total on filter "Pete_UK" <pashurst[ at ]auditel.net> 07.07.2006 10:12:50 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: [Quoted Text] > 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[ at ]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?

Re: Total on filter jk 07.07.2006 11:48:01 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: [Quoted Text] > 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[ at ]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? > >

Re: Total on filter Gord Dibben <gorddibbATshawDOTca> 07.07.2006 17:29:30 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[ at ]discussions.microsoft.com> wrote: [Quoted Text] >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[ at ]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? >> >>