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 - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now
Calculate special averages in Excel
Best Office Productivity Tools
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...
Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel (Full-Featured 30-Day Free Trial)
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! (Full-Featured 30-Day Free Trial)
