Using formulas within a filter

Occasional Visitor

Please help! I am charge with doing usage analysis in my company. We have usage stats and make a lot of use of filters. But if I am using a filter, how do I make a formula using only the numbers in a filter. For instance, I used a filter to only show one content type (book discussion guides). But when I try to use the auto sum button to show the average usage of book discussion guides, it makes a formula using all the types, including the ones I filtered out. I know I can use 101 to make a sum formula with only numbers in the filter. Is there a way to do this with averages, as well? I am tired of counting cells. 

2 Replies


See the SUBTOTAL and AGGREGATE functions.



I don't know which type of Filter you have been using. IF you are using the relatively new FILTER function you could nest that within a SUM or AVERAGE function and it would count only the cells that met the criteria in the FILTER function. Here are two good places to start to understand the FILTER function (which, by the way, requires the newest edition of Excel).



Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, but were limited to power users. With Dynamic Arrays we have rebuilt the calc engine, effectively turning all formulas into array formulas ...