AVERAGEIFS Function in Excel

Updated on December 26, 2023
Article byJeevan A Y
Edited byNannila Jai Ratna
Reviewed byDheeraj Vaidya, CFA, FRM

Excel AVERAGEIFS Function

AVERAGEIFS function in Excel is used to find the average from the target range of cells when more than one condition is met. For a single condition, we used AVERAGEIF, but for multiple conditions, we used AVERAGEIFS. It can take arguments where the first argument is the range column in which the average needs to be calculated. The rest are the criteria range and followed by criteria.

For example, we need to know the average score of female students in Physics. To calculate the average score based on the condition (female students), we can apply the AVERAGEIF function. In addition, we could employ the AVERAGEIFS function in Excel if there were more than one criterion.

Now, look at the syntax of the Excel AVERAGEIFS function, then we will move to the example section.

AVERAGEIFS Function in Excel syntax
  • Average Range: We need to take the average for the range of cells.
  • Criteria Range 1: We need to take the average out. So, in this argument, we need to select that column.
  • Criteria1: In Criteria Range 1, we need to consider the average for which value.
  • Criteria Range 2: This is the second criteria range to take the average out, so we need to select that second column in this argument.
  • Criteria2: In Criteria Range 2, we need to consider the average for which value.

Note: Logical value TRUE or FALSE is considered as 1 and 0. Even zero value is considered for average.

Examples

You can download this AVERAGEIFS Function Excel Template here – AVERAGEIFS Function Excel Template

Example #1

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Let us look at the example.

  1. Assume you want to take out the average sales of the product in a particular region in a specific month. Below is the data to demonstrate.


    averageifs example 1.1
    We need to take the region “North” average for the month “March in the above data.” So here, we need to look at two criteria to take the average out.

  2. Open the AVERAGEIFS function now.


    averageifs example 1.2

  3. Since we need to take the average Sales, select the “Sales” column for the Average Range argument.


    averageifs example 1.3

  4. The next argument is Criteria Range 1. So, select the 9*Region column as the reference.


    averageifs example 1.4

  5. We need the average of the East region for this criteria range, so mention Criteria 1 as East.


    averageifs example 1.4

  6. The next argument is Criteria Range 2. So, select the Month column as the reference.


    averageifs example 1.5

  7. Criteria 2 is derived from Criteria Range 2, for which month we need to take the average out. For example, we need the average for the Mar month, so we must mention the same in Criteria 2.


    averageifs example 1.6

  8. Now, we need to press Enter to get the result.


    averageifs example 1.7

So, the average sales for the region “East” for the month “Mar” is 2067.67.

Example #2

We can also use operator symbols in the criteria part of the Excel AVERAGEIFS function. For example, look at the below data.

example 2.1

We need to take the average sale between two dates from this data. So, first, we need to take the average sales between 27-Apr-2019 to 26-May-2019, then the average sales between 26-May-2019 to 17-Jun-2019.

We should supply these criteria with the logical operator in ExcelLogical Operator In ExcelLogical operators in excel are also known as the comparison operators and they are used to compare two or more values, the return output given by these operators are either true or false, we get true value when the conditions match the criteria and false as a result when the conditions do not match the criteria.read more symbols.

  • Let us open the AVERAGEIFS function in the D4 cell now.
example 2.2
  • We must select the Average Range as the “Sales” column.
example 2.3
  • Next, we must select Criteria Range 1 as a “Date” column.
example 2.5
  • Criteria 1 should be greater than 27-Apr-2019. So, first, we must mention the greater than symbol in double quotes, then combine this with the D3 cell.
AVERAGEIFS Function in Excel example 2.6
  • For Criteria Range 2, we must select the “Date” column.
AVERAGEIFS Function in Excel example 2.7
  • For Criteria 2, we need the average sales for the date lesser than 26-May -2019. So, enter less than symbols in double quotes and combine it with the E3 cell.
AVERAGEIFS Function in Excel example 2.8
  • Press the “Enter” key; we will get the average.
AVERAGEIFS Function in Excel example 2.9
  • So, the average sales between 27-Apr-2019 to 26-May-2019 is 4620.8. Similarly, we need the average sales between 25-May-2019 to 18-Jun-2019. Copy and paste the formula from the D4 cell to the D8 cell.
AVERAGEIFS Function in Excel example 2.10

We got the average sales as 3770.21. So, from this, we can conclude that average sales are more in the first period- 27-Apr-2019 to 26-May-2019.

We can use this function to take the average of numbers based on multiple criteria.

Recommended Articles

This article has been a guide to the AVERAGEIFS function in Excel. We discuss calculating the average numbers based on multiple criteria, examples, and downloadable templates. You can learn more about Excel functions from the following articles: –