https://www.exceldemy.com/how-to-find-mean-median-and-mode-on-excel/In this article, we will be showing how you can use the **AVERAGEIFS** function of Excel to calculate some averages while maintaining one or more criteria in Excel. To do so, we will go through **6** easy examples.

**AVERAGEIFS Function of Excel (Quick View):**

In the following picture, you can see the overview of the **AVERAGEIFS** function.

**Table of Contents**hide

**Download Practice Workbook**

You can download the following Excel file and practice while reading this article.

## Excel AVERAGEIFS Function: Syntax and Argument

**Summary**

- The
**AVERAGEIFS**function returns the average of the cells of an array that satisfy one or more given criteria. Here, the criteria can be of the same array or a different array. - Available from Excel 2007.

**Syntax**

The Syntax of the **AVERAGEIFS** function is:

`=AVERAGEIFS(average_range,criteria_range1,criteria1,...)`

**Argument**

Argument |
Required or Optional |
Value |
---|---|---|

average_range | Required | The array of cells whose average is to be determined. |

criteria_range1 | Required | The array of cells that needs to satisfy the first criteria. |

criteria1 | Required | The first criteria. |

criteria_range2 | Optional | The array of cells that needs to satisfy the second criteria. |

criteria2 | Optional | The second criteria. |

**Notes:**

- Only 1 criterion along with1 range of cells, where the criteria will be applied (
**criteria_range**), is essential. However, you can use multiple criteria if you need. - Both the
**criteria**and the**criteria_range**must come together like a pair. That means if you input**criteria_range 2**, you must input**criteria2**. - Both the
**average_range**and all**criteria_ranges**must be equal. Otherwise, Excel will raise**#VALUE!** - While calculating the average of values, Excel will count only those cell values that satisfy all the criteria.

**Return Value**

Returns the average of the cells of an array that satisfy one or more given criteria.

**Special Notes**

- If the criterion is equal to the cell value or cell reference, then in the place of criteria, you can simply put the value or the cell reference.

Like this:

`=AVERAGEIFS(C5:C9,C5:C9,1)`

OR

`=AVERAGEIFS(C5:C9,C5:C9,"Won")`

OR

`=AVERAGEIFS(C5:C9,C5:C9,A2)`

- When the criterion denotes greater than or less than some value, enclose the criteria within an
**apostrophe (“”)**

Like this:

`=AVERAGEIFS(C5:C9,C5:C9,">1")`

- When the criterion denotes greater than or less than some cell reference, enclose only the greater than or the less than symbol within an
**apostrophe (“”)**and then join the cell reference by an**ampersand (&)**

Like this:

`=AVERAGEIFS(C5:C9,C5:C9,">"&A2)`

- You can have partial matches too within the
**AVERAGEIFS**

For matching any one character in a specific place, use “**?”.**

For example, “**?end”** will match “**bend”**, “**send”** but not “**spend” **or** “end”.**

And for matching any number of characters including zero, use “***”**.

For example, “***end”** will match “**end”**, “**bend”**, “**send”**, “**spend”** all.

So the **AVERAGEIFS** formula will look like:

`=AVERAGEIFS(C5:C9,C5:C9,"?end")`

OR

`=AVERAGEIFS(C5:C9,C5:C9,"*end")`

- If any cell within the
**average_range**contains a text value other than a number,**AVERAGEIFS**will not count that even if it satisfies all the criteria. Because it is only possible to calculate the average of some numbers, not any text.

## 6 Examples of Using Excel AVERAGEIFS Function

The following data set has the **Opponent**, **Goals**, **Assists**, **Results**, and** Venue** columns. Furthermore, using this dataset, we will demonstrate** 6** examples to show the usages of the **AVERAGEIFS** function. Here, we used** Excel 365**. You can use any available Excel version.

### 1. Using Single Criteria for Equal To Value in AVERAGEIFS Function

In this example, we will show you how you can use the **AVERAGEIFS** function using single criteria equal to a value. Afterward, using the** AVERAGEIFS** function, we will find out the average **Goals** based on the criteria when the **Result** is **Won**.

Here, we have already marked the **Goals** and criteria **Won** with *yellow color*, and we will find out the average of the goals that have** a yellow color**.

**Steps:**

- First of all, we will type the following formula in cell
**H6**.

`=AVERAGEIFS(C6:C23,E6:E23,"Won")`

**Formula Breakdown**

**AVERAGEIFS(C6:C23,E6:E23,”Won”) →**Calculates the average of only those cells in the array**C6**to**C23**whose corresponding cells in the array**E6**to**E23**contain “**Won**”.**Output: 2.09**

- After that, press
**ENTER**.

As a result, you can see the result in cell **H6**.

**Read More:** **How to Use AVERAGE Function in Excel (5 Examples)**

### 2. Use of Single Criteria for Greater Than Value

In this example, we will show you how to use the **AVERAGEIFS** function to find out the** Goal**s that have **Assists** numbers** greater than or equal to 1**. Here, we have already marked the number of** Assists** that have a value **greater than or equal to 1** and the number of** Goals** based on the Assists with **yellow color**. Next, we will calculate the average of these goals based on the criteria.

**Steps:**

- To begin with, we will type the following formula in cell
**H6**.

`=AVERAGEIFS(C6:C23,D6:D23,">=1")`

**Formula Breakdown**

**AVERAGEIFS(C6:C23,D6:D23,”>=1″) →**Calculates the average of only those cells in the array**C6**to**C23**whose corresponding cells in the array**D6**to**D23**contain anything**greater than or equal to 1**.**Output: 1.80**

- Afterward, press
**ENTER**.

Therefore, you can see the result in cell **H6**.

**Similar Readings**

**How to Use LINEST Function in Excel (4 Suitable Examples)****Use RANK Function in Excel (With 5 Examples)****How to Use VAR Function in Excel (4 Examples)****Use PROB Function in Excel (3 Examples)****How to Use Excel STDEV Function (3 Easy Examples)**

### 3. Applying Multiple Criteria in AVERAGEIFS Function

In this example, we will demonstrate the use of the **AVERAGEIFS** function based on multiple criteria.

Here, we will find out the average of goals when** Goals** number is **at least 1**, and when the **Venue** is **Home**. We have marked both criteria with a **Yellow color**.

**Steps:**

- First, we will type the following formula in cell
**H6**.

`=AVERAGEIFS(C6:C23,C6:C23,">=1",F6:F23,"Home")`

**Formula Breakdown**

**AVERAGEIFS(C6:C23,C6:C23,”>=1″,F6:F23,”Home”) →**Calculates the average of only those cells in the array**C6**to**C23**that contain anything**greater than or equal to 1**and whose corresponding cells in the array**F6**to**F23**contain “**Home**”.**Output: 2.33**

- At this point, press
**ENTER**.

Therefore, you can see the result in **H6**.

Again, we will find out the average of goals when** the Goals** number is **greater than or equal to 1**, and when the **Assists **number is also **greater than or equal to 1**. We have marked both criteria with **Yellow color**.

- After that, we will type the following formula in cell
**H6**.

`=AVERAGEIFS(C6:C23,C6:C23,">=1",D6:D23,">=1")`

**Formula Breakdown**

**AVERAGEIFS(C6:C23,C6:C23,”>=1″,D6:D23,”>=1″) →**Calculates the average of only those cells in the array**C6**to**C23**that contain anything**greater than or equal to 1**and whose corresponding cells in the array**D6**to**D23**contain anything**greater than or equal to 1**.**Output: 2.33**

- At this point, press
**ENTER**.

Therefore, you can see the result in **H6**.

### 4. Counting Average with Partial Match (Wildcard Character)

In this example, we will show you how to calculate the average using the **AVERAGEIFS **function when the criteria match partially. We will use a** Wildcard character** for this purpose. You see, there are** two Korea** on the **Opponent** list, **North Korea **and **South Korea**. Next, we will find out the **average of the goals** of the **Opponent** that has **Korea** in their name. Here, we marked the **Opponent **and the corresponding **Goal **number with **Yellow color**.

**Steps:**

- First, we will type the following formula in cell
**H6**.

`=AVERAGEIFS(C6:C23,B6:B23,"*Korea")`

**Formula Breakdown**

**AVERAGEIFS(C6:C23,B6:B23,”*Korea”) →**Calculates the average of only those cells in the array**C6**to**C23**whose corresponding cells in the array**B6**to**B23**contain anything having “**Korea**” at the end.**Output: 2**

- Furthermore, press
**ENTER**.

Hence, you can see the result in cell **H6**.

If you want to know more about the **wildcard** characters, you can visit this link.

**Read More:** **The Different Ways of Counting in Excel**

### 5. Using Cell References in AVERAGEIFS Function

In this example, we will use the cell reference instead of text in the **AVERAGEIFS **function. We will use a single criterion for this purpose.

Here, we will find out the average **Goals** based on the criteria when the **Result** is **Won**. In the formula, instead of typing **Won**, we will simply select cell **E6**.

We have already marked the **Goals** and criteria **Won** with *yellow color*, and we will find out the average of the goals that have** a yellow color**.

**Steps:**

- First of all, we will type the following formula in cell
**H6**.

`=AVERAGEIFS(C6:C23,E6:E23,E6)`

**Formula Breakdown**

**AVERAGEIFS(C6:C23,E6:E23,E6) →**Calculates the average of only those cells in the array**C6**to**C23**whose corresponding cells in the array**E6**to**E23**contain the cell content of cell**E6**that is “**Won**”.**Output: 2.09**

- After that, press
**ENTER**.

As a result, you can see the result in cell **H6**.

### 6. Applying Date Range in AVERAGEIFS Function

Here, we will show you the use of the **AVERAGEIFS **function when there is a** Date** range, and we want to find out the average based on **Dates**. For this purpose, we modified the previous dataset and added a **Date** column to it.

After that, we want to find the average of the goals that include the date from **20-Mar-22** to **08-Aug-22**. Here, we marked these dates with **Yellow color**.

**Steps:**

- In the beginning, we will type the following formula in cell
**H6**.

`=AVERAGEIFS(C6:C23,F6:F23,"<=8-Aug-22",F6:F23,">=20-Mar-22")`

**Formula Breakdown**

**AVERAGEIFS(C6:C23,F6:F23,”<=8-Aug-22″,F6:F23,”>=20-Mar-22″) →**Calculates the average of only those cells in the array**C6**to**C23**whose corresponding cells in the array**F6**to**F23**contain dates greater than or equal to**20-Mar-22**and less than or equal to**8-Aug-22**.**Output: 1.727272727**

- At this point, press
**ENTER**.

Therefore, you can see the result in **H6**.

## Common Errors with Excel AVERAGEIFS Function

In the following table, we have shown the common errors of the **AVERAGEIFS** function, and the reasons for occurring such errors.

Error |
When They Show |
---|---|

#DIV/0! | Shows when no value in the average_match matches all criteria. |

#VALUE! | This shows when the lengths of all the arrays are not the same. |

## Practice Section

You can download the above excel file and, therefore, practice the explained examples.

## Conclusion

Here, we tried to show you** 6 **examples of the usage of the **AVERAGEIFS** function in **Excel**. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website **Exceldemy** to explore more.

**Related Articles**

**How to calculate Average, Median, & Mode in Excel****Use SMALL Function in Excel (4 Common Examples)****How to Use MIN Function in Excel (5 Relevant Examples)****How to use MAX function in Excel (6 Examples)****Use LARGE Function in Excel (6 Easy Examples)****How to Use COUNTIF Function in Excel (10 Suitable Applications)**