Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter data & sum or avg the results?
Excel 2003. I have a worksheet with a data table of sales activity with
column headings like Sales Region, Product Type, Order Type, # of Units, and Price (plus several others that provide extra detail). In the rows above the data table I sum the total number of units ordered, the total dollar value, and the average price. What I need to do is allow filtering of the data table while still allowing the formulae/formulas to work (recalculate). The end users should be able to select a certain product type or sales region to filter the data table, and have the formulas re-calculate. I use pivot tables on a separate sheet for a simple summary of the info, but on this page I want the entire set of extra columns also included in the data table so that each record has all of the appropriate info displayed for regional sales managers. When using simple auto-filter drop down menus, the formulas don't recalculate. I used the Create List feature and it worked (filtered list based on column selections and recalculated the subtotals) but when I emailed the file to other people the filter arrows don't even show up. Help. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter data & sum or avg the results?
Ok, one of you folks tipped me off to the subtotal function so that part is
solved -- but I noticed that Countif is not one of the functions. Any way around that? "bryan stewart" wrote: Excel 2003. I have a worksheet with a data table of sales activity with column headings like Sales Region, Product Type, Order Type, # of Units, and Price (plus several others that provide extra detail). In the rows above the data table I sum the total number of units ordered, the total dollar value, and the average price. What I need to do is allow filtering of the data table while still allowing the formulae/formulas to work (recalculate). The end users should be able to select a certain product type or sales region to filter the data table, and have the formulas re-calculate. I use pivot tables on a separate sheet for a simple summary of the info, but on this page I want the entire set of extra columns also included in the data table so that each record has all of the appropriate info displayed for regional sales managers. When using simple auto-filter drop down menus, the formulas don't recalculate. I used the Create List feature and it worked (filtered list based on column selections and recalculated the subtotals) but when I emailed the file to other people the filter arrows don't even show up. Help. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter data & sum or avg the results?
There would be several ways, one would be to use a help column and
if for instance you want to count cells that are greater than 5 you could add a formula for each cell when the the list is not filtered like =B25 then do the filter and add another filter on the help column and select TRUE, then use the subtotal(3,range) to count visible cells or in one fell swoop like with this method http://tinyurl.com/yksqkl Regards, Peo Sjoblom bryan stewart wrote: Ok, one of you folks tipped me off to the subtotal function so that part is solved -- but I noticed that Countif is not one of the functions. Any way around that? "bryan stewart" wrote: Excel 2003. I have a worksheet with a data table of sales activity with column headings like Sales Region, Product Type, Order Type, # of Units, and Price (plus several others that provide extra detail). In the rows above the data table I sum the total number of units ordered, the total dollar value, and the average price. What I need to do is allow filtering of the data table while still allowing the formulae/formulas to work (recalculate). The end users should be able to select a certain product type or sales region to filter the data table, and have the formulas re-calculate. I use pivot tables on a separate sheet for a simple summary of the info, but on this page I want the entire set of extra columns also included in the data table so that each record has all of the appropriate info displayed for regional sales managers. When using simple auto-filter drop down menus, the formulas don't recalculate. I used the Create List feature and it worked (filtered list based on column selections and recalculated the subtotals) but when I emailed the file to other people the filter arrows don't even show up. Help. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help; data filter | Excel Discussion (Misc queries) | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
How we can copy data when there is filter | Excel Discussion (Misc queries) | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |