If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Total on filter
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 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? |
#3
|
|||
|
|||
Total on filter
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 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? |
#4
|
|||
|
|||
Total on filter
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 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? |
#5
|
|||
|
|||
Total on filter
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 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 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? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Data filter total | SCOOBYDOO | Worksheet Functions | 2 | February 13th, 2006 09:38 AM |
Troublesome query | RWhittet | Running & Setting Up Queries | 4 | January 22nd, 2006 06:15 AM |
Reporting subreport total on main report | BobV | Setting Up & Running Reports | 22 | November 1st, 2005 03:19 AM |
Toolbars, Drop-Down Menus | Rick | New Users | 1 | September 21st, 2005 11:17 AM |
Need to clear controls of Filter form | Jan Il | Using Forms | 2 | November 28th, 2004 02:04 PM |