This guide will explain **how to use SUBTOTAL with AVERAGEIF in Excel**.

##### Table of Contents

The rules for using the `SUBTOTAL`

function in Excel are the following:

- The nested subtotals are ignored to avoid double counting when there are other subtotals within ref1, ref2, or nested subtotals.
- The function will ignore any rows that are not included in the result of a filter no matter which function_num we use.
- The
`SUBTOTAL`

function does not support rows of data or horizontal ranges. - When any of the references are 3-D references, the function returns the #VALUE! error.

Excel is an excellent tool for organizing and calculating data. Whenever we have a large data set of values we need to calculate the total sum or average, the process is easier to perform in Excel due to built-in functions and tools.

But, sometimes we need to calculate only certain data in a large data set. So this is where we use the filter tool in Excel to only display the data values we need from the larger data set.

Usually, we would use the `AVERAGEIF`

function to calculate the average of the data values. However, we get the total sum or average of the entire data set rather than just the filtered data values.

Because the function calculates all the data values from the original data set, we can’t get the correct result from the filtered data. So we need another formula to use with the `AVERAGEIF`

function to calculate what we need from the filtered data set correctly.

In this case, we can combine the `SUBTOTAL`

function with the `AVERAGEIF`

function to create a formula that will calculate the average of the filtered data set instead of the original data set.

Let’s take an example wherein we need to use `SUBTOTAL`

with `AVERAGEIF`

in Excel.

Suppose you have a master list of all your students’ scores from different classes. And you need to calculate the class average for each gender. Since you do not want to create a new table for each class, you opted to filter out the specific class you want to calculate the average.

However, you cannot get the correct result since the function includes the data values from the original data set instead of just the filtered ones. Instead, you combine `SUBTOTAL`

with `AVERAGEIF`

to get the correct result.

Before we move on to a real example, let’s first learn the syntax of the two functions in Excel.

**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 can activate any function in Excel.**SUBTOTAL()**refers to our`SUBTOTAL`

function. This function is used to return the subtotal in a list or database.**function_num**is a required argument. And it refers to a number from 1 to 11 that will specify the summary function for the subtotal.**ref1**is another required argument. So it refers to 1 to 254 rangers or cell references for which we want to find the subtotal.

**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 we begin any function in Excel.**AVERAGEIF()**is our`AVERAGEIF`

function. So this function is used to find the average or arithmetic mean for the selected cells based on a given condition or criteria.**range**is a required argument. And it refers to the range of cells we want to be evaluated.**criteria**is also a required argument. So it refers to the condition or criteria in the form of a number, expression, or text that defines which cells we want to use to find the average.**average_range**is an optional argument. And it refers to the actual cells to be used to find the average. If left blank, the cells in the range are used.

Great! Now let’s discuss a real example of using `SUBTOTAL`

with `AVERAGEIF`

in Excel.

**A Real Example of Using SUBTOTAL with AVERAGEIF in Excel**

Let’s say we have a data set containing a list of the class section, the gender of the student, and their scores. And we want to calculate the average of the girls’ scores in a specific class. So our initial data set would look like this:

Firstly, we need to filter our original data set. Since we only want to calculate the average of a specific class, we will filter the data set only to show that specific class.

Once we have filtered out the data set, we are left with the specific class we want to calculate the average of. In this case, we want to focus on the average score of the girls in this class.

If we only use the `AVERAGIF`

function alone, the function will calculate all the girls’ scores, including those from the other classes. To prevent this, we will combine the `AVERAGEIF`

function with the `SUBTOTAL`

function to correctly calculate only the filtered values.

And 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 explain the steps of how to use `SUBTOTAL`

with `AVERAGEIF`

in Excel.

**How to Use SUBTOTAL with AVERAGEIF in Excel**

In this section, we will explain the step-by-step process of how to use `SUBTOTAL`

with `AVERAGEIF`

in Excel. Furthermore, each step contains pictures and detailed instructions to guide you along the way.

1. Firstly, we need to filter our data set. In this case, we only want to display the values from class **A**. To do this, we will first select the entire data set and go to the **Data **tab. Then, we will click the **Filter **icon.

2. Secondly, we will click the dropdown arrow beside the **Class **column. Next, we will check the box beside **A **to only display those data values. Then, we will click **Apply**.

3. Thirdly, we can now use a combined `SUBTOTAL`

and `AVERAGEIF`

formula to calculate the average of the filtered data values. So we will type in the formula “**=AVERAGE(IF(SUBTOTAL(2, OFFSET(D2, ROW(D2:D10) – ROW(D2), 0)), IF(C2:C10=”girl”,D2:D10)))**”.

Lastly, we will press the **Enter **key to return the result.

4. And tada! We have correctly calculated the average of the filtered data values.

And that’s pretty much it! We have explained how to use `SUBTOTAL`

with `AVERAGEIF`

in Excel. Now you can apply this learning whenever you need to calculate the average values of a filtered data set.

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

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.