Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
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
Posted to microsoft.public.excel.setup
|
|||
|
|||
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
Posted to microsoft.public.excel.setup
|
|||
|
|||
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
Posted to microsoft.public.excel.setup
|
|||
|
|||
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
Posted to microsoft.public.excel.setup
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data filter total | Excel Worksheet Functions | |||
Percent of total in Pivot tables | Excel Worksheet Functions | |||
sum of total hours not correct | Setting up and Configuration of Excel | |||
Macro for Filter Switches ... Maybe??? | Excel Discussion (Misc queries) | |||
Filter Switches vs Macro? | Excel Discussion (Misc queries) |