How to Average Filtered Data in Excel (2 Easy Methods)

We will consider an example where we have a list of expenditures within different categories shown in the picture below.

We filtered the list based on the category Food and Health.

Method 1 – Use of the SUBTOTAL Function to Average Filtered Data in Excel

Steps:

Select any cell and input the following formula.

=SUBTOTAL(1,D5:D11)

Here, 1 is for calculating the Average and D5:D11 is the filtered range from where the average will be calculated.

Press Enter.

Method 2 – Use of the AVERAGEIF Function to Average Filtered Data in Excel

The problem with the SUBTOTAL function is that when you undo filtering of the range, the average will get changed. To fix the issue, we can use the AVERAGEIF function so that our calculated average remains unchanged. But this time, we can only filter one category. So either Food or Health should be filtered, not both. We will filter only the Food category.

Steps:

Select any cell and input the following formula.

=AVERAGEIF(C5:C7,"Food",D5:D7)

Press Enter and you will have the average of the filtered cells.

Things to Remember

The result from the SUBTOTAL function is subject to change as the filtered range changes.
The AVERAGEIF function takes only one criterion to calculate the average of filtered cells.

Download the Practice Workbook

Average of Filtered Cells.xlsx