## What Is AVERAGEIFS Function In Excel?

The

AVERAGEIFS Function in Excelcalculates the average/mean of the numeric values that match multiple criteria given for a selected dataset. The logical operators (>,<,<>,=) and wildcards (*,?) can be used by theAVERAGEIFSfunction just like theSUMIFSandCOUNTIFSfunctions. TheAVERAGEIFS Excel functionis an inbuiltStatisticalfunction, so we can insert the formula from the “Function Library” or enter it directly in the worksheet.

For example, the following image shows values in columns A and B. We will calculate the average of the numbers based on set criteria using the **AVERAGEIFS Excel function .**

Select cell B8, enter the formula ** =AVERAGEIFS(B2:B7,A2:A7,”A”), **and press “Enter”. The result is ‘

**59.333**’, as shown below.

###### Key Takeaways

- The
**AVERAGEIFS Excel function**helps users provide multiple criteria for a given dataset, finds the cells that fulfill all the criteria, and calculates the average of the respective cell values. - The function treats the cell as a ‘0’ value if any cell in the criteria range is empty.
- The AVERAGEIFS evaluates the cell range that contains TRUE as 1, and FALSE as 0.
- We can use the conditional operators (>,<,<>,=) or special characters (?,*) to match partial conditions or to include certain conditions in the formula.

### AVERAGEIFS() Excel Formula

The syntax of the **AVERAGEIFS Excel formula **is,

The arguments of the **AVERAGEIFS Excel formula **are,

It is a mandatory argument. It is the cell rage to find the average.*average_range:*It is a mandatory argument. It is a cell range to apply the criteria.*criteria_range1*:It is a mandatory argument. It is the first criteria for the first cell range from the given dataset, followed by*criteria1*:and so on*[criteria_range2,criteria2], [criteria_range3,criteria3],**…*

### How To Use AVERAGEIFS Excel Function?

We can use the **AVERAGEIFS function in Excel** in two ways, namely,

**Access from the Excel ribbon.****Enter in the worksheet manually.**

#### Method #1 – Access from the Excel ribbon

Choose an empty cell for the output → select the **“Formulas” **tab → go to the “**Function Library**” group → click the **“More Functions” **option drop-down → click the **“Statistical” **option right arrow →** s**elect the **“AVERAGEIFS”** function, as shown below.

The **“Function Arguments” **window appears. Enter the arguments in the **“Average_range”, “Criteria_range1”**, and **“criteria” **fields → click “**OK**”, as shown below**.**

[**Note:** To view the “Criteria1” field, click the **“Criteria_range1” **field.]

#### Method #2 – Enter in the worksheet manually

- Select an empty cell for the output.
- Type
**=AVERAGEIFS(**in the cell. [Alternatively, type**=A**and double-click the**AVERAGEIFS**function from the Excel suggestions.] - Enter the arguments as cell values or cell references in Excel.
- Close the brackets of the formula.
- Press the “
**Enter**” key.

Let us take a basic example to understand this function.

Here, we will calculate the average of the numbers based on set criteria using **AVERAGEIFS Excel function **for the zonal-wise employee sales.

In the table, the data is,

- Column A contains the Zone.
- Column B contains the Employee.
- Column C contains the Sales.

The steps to calculate the average **using** **AVERAGEIFS Excel function **are,

**Select cell C14, and enter the formula =AVERAGEIFS(C2:C13, i.e., the average_range value.****In continuation with the formula, enter the value of ‘criteria_range1’ as A2:A13. So, the formula is =AVERAGEIFS(C2:C13,A2:A13,****In continuation with the formula, enter the value of ‘criteria1’ as North, and close the brackets. The complete formula is =AVERAGEIFS(C2:C13,A2:A13,”North”), as shown below.****Press the “Enter” key. The result is “$54,93,657”, as shown below.**

### Examples

We will understand some advanced scenarios using **AVERAGEIFS Excel function examples.**

#### Example #1

We will calculate the average of the numbers based on set criteria using the **AVERAGEIFS function** for the product quantity.

In the table, the data is,

- Column A contains the Product.
- Column B contains the Quantity.

The procedure to calculate the average using the **AVERAGEIFS Excel function **is,

Select cell **B7**, enter the formula ** =AVERAGEIFS(B2:B6,A2:A6,”Bakery”), **and press the “

**Enter**” key.

The result is “**6566**”, as shown above.

#### Example #2

We will calculate the average of the numbers based on set criteria using **AVERAGEIFS Excel function **for the student marks.

In the table, the data is,

- Column A contains the Class.
- Column B contains the Marks.

The procedure to calculate the average using the **AVERAGEIFS Excel function **is,

Select cell **B7**, enter the formula ** =AVERAGEIFS(B2:B6,A2:A6,”Class I”), **and press the “

**Enter**” key.

The result is “**7.667**”, as shown above.

#### Example #3

We will calculate the average of the numbers based on multiple criteria using the **AVERAGEIFS** formula for the medals won by countries in the tennis tournament, and

In the table, the data is,

- Column A contains the Countries.
- Column B contains the Result.
- Column C contains the Medals.

The steps to calculate the average with multiple criteria using the **AVERAGEIFS Function **are,

**Step 1:**Select cell**C7**, enter the formula*=AVERAGEIFS(C2:C6,B2:B6,”Won”*

[**Note:** The first criteria, i.e., ** criteria1,** is to check for “

**Won**” in the

*criteria_range1,*B2:B6.]**Step 2:**In continuation with the formula, select the criteria_range2, A2:A6, and the second criteria, i.e.,as*criteria2****a*,**which will check for the country names having the letter ‘**a**’**,**and close the brackets.

Therefore, the complete formula is** =AVERAGEIFS(C2:C6,B2:B6,”=Won”,A2:A6,”*a*”)**

**Step 3:**Press the “**Enter**” key.

The result is “**6.333333333**”, as shown above.

[**Output Observation:** The formula first checks whether cell range **C2:C6** satisfies both conditions.

**Condition 1:**The cells**C2, C4,**and**C5**, have the text string “**Won**” in their cells**B2, B4,**and**B5,**respectively.**Condition 2:**Their respective countries in cells**A2, A4,**and**A5**, i.e., Br**a**zil, It**a**ly, and Indi**a**, have the letter ‘**a**’ in their names.

Therefore, the cell values that satisfy both conditions are **C2, C4, **and** C5. **Now, the formula calculates the average,i.e.,** (C2+C4+C5)/3, **which is** (5+4+10)/3 **or** 19/3=6.33333.]**

### Important Things To Note

- The
**“#DIV/0!”**error occurs when:- The “
” argument is left blank or is a text value.*average_range* - None of the criteria are met.

- The “
- Each “
” provided in the formula must be the same size, else we will get the*criteria_range***“****#VALUE!”**error. - We will get a
**“#NAME?”**error if the function’s name is incorrectly entered in the formula or if we do not close the brackets of the formula.

### Frequently Asked Questions (FAQs)

**1. What does the AVERAGEIFS function do in Excel?**

**The AVERAGEIFS function** calculates the average of cells that fulfills all the given criteria or conditions.

The syntax of the** AVERAGEIFS function **is *=AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2,criteria2]…)*

**2. How to use Excel AVERAGEIFS function?**

The step-by-step procedure to calculate the average using the **AVERAGEIFS Excel function** is,**1. **Select an empty cell for the output.**2. **Type **=AVERAGEIFS( **in the cell. [Alternatively, type **=A **and double-click the **AVERAGEIFS **function from the Excel suggestions.]**3. **Enter the arguments as cell values or cell references.**4. **Close the brackets of the formula.**5. **Press the “**Enter**” key.

For example, we will calculate the average of the numbers based on set criteria using the **AVERAGEIFS** formula for the people’s work done.

In the table, the data is,**• **Column A contains the Name.**• **Column B contains the Value.

The procedure to calculate the average using the **AVERAGEIFS Excel function **is,

Select cell **B8**, enter the formula ** =AVERAGEIFS(B2:B7,A2:A7,”Arnald”), **and press the “

**Enter**” key.

The result is “

**50**”, as shown above.

**3. Where is the AVERAGEIFS function in Excel?**

The **AVERAGEIFS Excel function **is found in the **Formulas** tab” as follows:

Choose an empty cell for the output → select the **“Formulas” **tab → go to the “**Function Library**” group → click the **“More Functions” **option drop-down → click the **“Statistical” **option right arrow → select the **“AVERAGEIFS”** function, as shown below.

### Download Template

This article must help understand the **AVERAGEIFS Excel function**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to AVERAGEIFS Excel Function. Here we explain how to use AVERAGEIFS formula along with examples & downloadable excel template. You can learn more from the following articles –

## Leave a Reply