Sometimes, while working with data, you may need to perform an average calculation based on some criteria or conditions. Doing this manually may require great time and effort from you. In such instances, Google Sheets provides the **AVERAGEIFS** function to calculate the average based on some condition. In this article, we will try to show you, how to use **AVERAGEIFS** in Google Sheets easily and effectively.

**Table of Contents**hide

## A Sample of Practice Spreadsheet

Download this practice spreadsheet to practice yourself:

## What Is **AVERAGEIFS** **Function in Google Sheets**?

**AVERAGEIFS** is a function to use when you want to calculate the average of some data based on conditions. The condition may be one or more than one.

#### Syntax

The syntax of **AVERAGEIFS** is:

`AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])`

#### Arguments

ARGUMENT | REQUIREMENT | FUNCTION |
---|---|---|

average_range |
Required | The data range to calculate the average from. |

criteria_range1 |
Required | The range to check up against criterion1. |

criterion1 |
Required | The condition that is to apply to criteria_range1. |

criteria_range2, criterion2, … |
Optional | Additional ranges and criteria to check up |

#### Output

The formula ** =AVERAGEIFS(D5:D14,C5:C14,"Male")** will show the average age of

**Males**which is

**23.33**within the range

**D5:D15**, where

**C5:C14**contains the Male or Female data.

## 4 Practical Examples to Use **AVERAGEIFS** Function in Google Sheets

### 1. Find Average with Single Condition

The dataset we will use to calculate **AVERAGEIFS** with a single condition has the **Names** of some people along with their **Gender** and **Age**. We will calculate the *average age of all Females* with the **AVERAGEIFS** function.

**Steps**:

- First, select the cell where you want to show the average. For our example, we go to cell
**D16**.

- After that, type
**=AVERAGEIFS**with opening parentheses in the formula box. We type**=AVERAGEIFS**in our example.

- Then, type the range where you want to calculate the average followed by a
**comma**. We type the range**D5:D14**.

- Now, type the range of conditions that the
**AVERAGEIFS**function will match based on criterion1 followed by a**comma**. We type**C5:C14**.

- After that, type in the condition based on which you want to calculate the average for and close parentheses. We type
**“Female”**as we want this to be our condition. - The final formula is:

`=AVERAGEIFS(D5:D14,C5:C14,"Female")`

**Formula Explanation:**

**D5:D14**is the range from where we want to calculate the average in this case the column**Age**.**C5:C14**is the range of criteria to check up against*criterion1*in this case the column**Gender**.**“Female”**is the condition based on the function.

- Finally, press
**ENTER**and you will see the average age of all*Females*calculated.

### 2. Utilizing Multiple Conditions to Get Average

The dataset we will use for this example is almost the same as the one we used in the previous method with an added column of weight in **Kgs**. In this example, we will calculate the average age of females who weigh *more than 60 Kg* which is a numerical condition. In the previous example, the condition was a text condition.

**Steps**:

- First, go to the cell where you want the average calculation to be. For our example, we go to cell
**D16**.

- Then, type in the formula:

`=AVERAGEIFS(E5:E14,C5:C14,"Female",D5:D14,">21")`

**Formula Explanation:**

**D5:D14**is the range from where we want to calculate the average from.**C5:C14**is the range of criteria to check up against*criterion1*.**“Female”**is the first condition. As**Female**is not a value, it should be within a quotation mark.**D5:D14**is the second range of the second condition that we want to apply.**“>21”**is the second condition of the ages.

**Note**: It is possible to use as many conditions as you like, one range and condition after another in the formula box to get the desired result you want.

- Finally, press
**ENTER**and you will see the average age of all*Females with age over 21*is calculated.

### 3. Using Date as a Condition for Average

A date can be used as a condition for the average with the **AVERAGEIFS** function.

For this, we have a dataset with **Names**, **Gender**, **Age,** and **Joining Date** of some employees. The date format we use is *MM/DD/YYYY*. You can use any format according to your preference. We want to calculate the average age of the Females who joined on *11/1/2022*.

**Steps**:

- First, go to the cell where you want the calculation to take place. We go to cell
**D16**.

- Then, type in the following formula:

`=AVERAGEIFS(D5:D14,C5:C14,"Female",E5:E14,DATE(2022,11,1))`

**Formula Explanation:**

**D5:D14**is the range from where we want to calculate the average from.**C5:C14**is the range of criteria to check up against*criterion1*.**“Female”**is the first condition for our average calculation. As**Female**is not a value, it should be within a quotation mark.**E5:E14**is the range of the second condition where the date is located.**DATE(2022,11,1)**is the condition based on the date. Notice that, the date format should be in (YYYY,MM,DD).

- Finally, press
**ENTER**and the average age of all*Females on 11/1/2022*is calculated.

### 4. Applying OR Criteria

The **OR** criteria cannot be used with **AVERAGEIFS**. To get around that, we can use two or multiple **AVERAGEIFS** conditions to *add the results* to get the desired output.

Let’s say we want to calculate the average age of **Males** who are *below the age of 22* **or** **Females** who are *above the age of 22*.

**Steps**:

- First, we type
for the`=AVERAGEIFS(D5:D14,C5:C14,"Male",D5:D14,"<22")`

**Males**who are*below the age of 22.*

- Now, we type
**+**to add the condition to the second one. In this case, the**+**sign does the work of the**OR**criteria.

- After that, we type the second condition:

** AVERAGEIFS(D5:D14,C5:C14,"Female",D5:D14,">22") which** is the formula for

**Females**who are

*above the age of 22*.

- Then, the final formula is:

`=AVERAGEIFS(D5:D14,C5:C14,"Male",D5:D14,"<22")+AVERAGEIFS(D5:D14, C5:C14, "Female",D5:D14, ">22")`

- Finally, press
**ENTER**to get the final result.

- The answer that we get is
. Thus, we have to*mathematically wrong***divide**the result by**2**to get the actual result.

`=(AVERAGEIFS(D5:D14,C5:C14,"Male",D5:D14,"<22")+AVERAGEIFS(D5:D14,C5:C14,"Female",D5:D14,">22"))/2`

## Possible Problems and Solutions

- If there is any unwanted space or whitespace the calculation may be wrong. You can remove any unwanted space with the
**TRIM**function. - If the condition you used is not a value, the calculation may show an error. So, be careful to use it within a quotation mark. Note that, the value should also be within a quotation mark if you use any conditions using
**<**,**>**, or**=**.

## Conclusion

In this article, we showed you how to use **AVERAGEIFS** in Google Sheets with different examples. Keep practicing the examples that we have shown here for a better understanding of the concept. We hope this article was useful to you to help you.

Also, check out other articles on **OfficeWheel** to keep on improving your Google Sheets work knowledge.