This guide will discuss **how to average filtered rows in Excel using two easy and simple methods**.

##### Table of Contents

The rules for using the `AVERAGEIF`

function in Excel are the following:

- The
`AVERAGEIF`

function is used to calculate the average of numbers in a selected range that meets certain criteria or conditions. Additionally, we can utilize the function to average cells based on dates, numbers, and text values. - The function supports logical operators and wildcard characters to input criteria or conditions.
- When the range has TRUE and FALSE values, the function will ignore them when calculating the average. Empty cells are also ignored when calculating the average.
- If no cells in the range meet the criteria, the function will return a #DIV/0! error.
- The function does not support arrays. So we can only supply a range.
- Although it supports wildcard characters, the function is not case-sensitive.

Since it has several built-in functions and tools, Excel is a popular tool to use when working with data, especially large quantities of data. Moreover, we can easily manipulate, organize, and arrange our data set in Excel. For example, we can apply a filter to our data set.

So the filter tool is a very versatile and useful feature in Excel that allows us to hide rows and only display specific rows in the data set we want to focus on. In this guide, we will focus on learning how to average filtered rows in Excel.

Luckily, there are two easy and simple methods to get the average of filtered rows in Excel. Firstly, we can use the `AVERAGEIF`

function. Secondly, we can utilize the `SUBTOTAL`

function.

Let’s take a sample scenario wherein we need to determine the average of filtered rows in Excel.

Suppose you have a sales report containing the number of sales made on different dates. And you are tasked to calculate the average sales for the month of October only. To do this, you first filtered the data set to only display the rows from October.

Then, you utilized the `AVERAGEIF`

function to get the average number of sales for the month of October, which are the filtered rows.

Before we move on to a real example of averaging filtered rows in Excel, let’s first understand how to use the different functions.

**The Anatomy of the AVERAGEIF Function**

The syntax or the way we write the `AVERAGEIF`

function is as follows:

=AVERAGEIF(range, criteria, [average_range])

Let’s take apart this formula and understand what each term means:

**=**the equal sign is how we activate any function in Excel.**AVERAGEIF()**is our`AVERAGEIF`

function. And this function is used to find the average or arithmetic mean for the cells that meet the specific criteria or conditions we give.**range**is a required argument. So this refers to the range of cells we want to evaluate.**criteria**is another required argument. And this refers to the condition or criteria in the form of a number, expression, or text that will define which cells to use to find the average.**average_range**is an optional argument. So this refers to the actual cells to be used to find the average. When omitted, the cells in the range are used.

**The Anatomy of the SUBTOTAL Function**

The syntax or the way we write the `SUBTOTAL`

function is as follows:

=SUBTOTAL(function_num, ref1,...)

Let’s take apart this formula and understand what each term means:

**=**the equal sign is how we begin any function in Excel.**SUBTOTAL()**refers to our`SUBTOTAL`

function. And this function is used to return a subtotal in a list or database.**function_num**is a required argument. So this refers to the number 1 to 11 that will specify the summary function for the subtotal.**ref1**is another required argument. And this refers to 1 to 254 ranges or references for which we want to get the subtotal.

**Note**: The `SUBTOTAL`

function works best with vertical data.

Great! Now we can dive into a real example of averaging filtered rows in Excel.

**A Real Example of Averaging Filtered Rows in Excel**

Let’s say we have a data set containing the number of sales made on different dates each month. And we also have a column that shows the category of the sale made. So our initial data set would look like this:

Our main goal is to get the average number of sales made for the month of October. However, our data set shows the number of sales made for the months of October, November, and December. Before we can calculate the average, we would first need to filter out our data set.

To do this, we can simply go to the data tab and apply a filter to our data set. Afterward, we can choose to only display the rows from the month of October and hide the other rows. By doing this, we have filtered out the rows we need to calculate the average.

So there are two simple methods we can use to get the average of the filtered rows. Firstly, we can use the `AVERAGEIF`

function, which will allow us to get the average of the filtered rows even when we take off the filter.

But, the downside of this method is that we can only use one criterion. If the filtered rows had different categories, we would need to get the average separately.

Secondly, we can utilize the `SUBTOTAL`

function, allowing us to get the average even if we have different categories. However, the downside of this method is the average will change when we undo the filter.

So our final data set would look like this:

You can make your own copy of the spreadsheet above using the link attached below.

Amazing! Now we can proceed to discuss the process of how to average filtered rows in Excel.

**How to Average Filtered Rows in Excel**

In this section, we will discuss the step-by-step process of how to average filtered rows in Excel using two simple and easy methods. Furthermore, each step contains detailed instructions and pictures to help you along the way.

1. Firstly, we need to filter out the rows we need to average. To do this, we will select the entire data set and go to the **Data **tab. Next, we will click the **Filter **icon.

2. Secondly, we will click the dropdown arrow beside the Date header column. Then, we will unselect all the boxes except for **October**. Lastly, we will click **Apply**.

3. Thirdly, we can now get the average of the filtered rows. To do this, we can type in the formula “**=AVERAGEIF(C2:C4, “Food”, D2:D4)**”. Lastly, we will press the **Enter **key to return the result.

4. Alternatively, we can use another formula to get the average. So we will input the formula “**=SUBTOTAL(1, D2:D4)**”. Then, we will press the **Enter **key to return the result.

5. And tada! We have successfully averaged the filtered rows in Excel.

And that’s pretty much it! We have successfully discussed how to average filtered rows in Excel using two simple and easy methods. Now you can choose any of the methods and apply them to your own work whenever you need to get the average of filtered cells.

Are you interested in learning more about what Excel can do? You can now use the `AVERAGEIF`

function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.