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.

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.

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.

Filter data & sum or avg the results?
 

Bryan

Use the SUBTOTAL function on filtered data.

=SUBTOTAL(9,range) for Sum

=SUBTOTAL(1,range) for Average

See help on "subtotal" for more parameters.


Gord Dibben MS Excel MVP


On Thu, 4 Jan 2007 09:09:00 -0800, bryan stewart <bryan
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.