How to average filtered cells/list in Excel?
Average function is quite useful for our daily works in Excel. This article will talk about how to average filtered data/list in Excel easily.
Average filtered data/list in Excel
Average filtered cells/list with formulas
If the AVERAGE function is applied in the filtered data or list, it will not get the correct average. In this condition, the SUBTOTAL function can help you average filtered data/list in Excel. Supposing you have filtered your table as following screen shots shown, you can get its average as follows:
In a blank cell enter the formula =SUBTOTAL(1,C12:C24) (C12:C24 is the range with all filtered data in Amount column) and press the Enter key.
Sum/Count/Average visible cells only in a specified range with ignoring hidden or filtered cells/rows/columns
The normally SUM/Count/Average function will count all cells in the specified range on matter cells are hidden/filtered or not. While the Subtotal function can only sum/count/average with ignoring hidden rows. However, Kutools for Excel SUMVISIBLE/COUNTVISIBLE/AVERAGEVISIBLE functions will easily calculate the specified range with ignoring any hidden cells, rows, or columns.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now
Calculate special averages in Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!