How to Average Filtered Rows in Excel

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

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:

Initial dataset

 

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:

Final dataset

 

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.

Apply a filter

 

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.

Filter the rows

 

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.

Average Filtered Rows in Excel

 

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.

Average Filtered Rows in Excel

 

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

Average 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.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like