Sometimes you may need to use **the AVERAGEIF function** with multiple criteria in Excel. So, if you are looking for the use of an **AVERAGEIF** function with multiple criteria in Excel then you have come to the right place. In this article, I will demonstrate how to use the **AVERAGEIF** function with multiple criteria in Excel.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from here:

## 5 Examples to Use AVERAGEIF with Multiple Criteria in Excel

Here, I will describe **5 **suitable examples of how to use an **AVERAGEIF** function with multiple criteria in Excel. In addition, for your better understanding, I’m going to use a sample dataset.

Moreover, the sample dataset has** 4 columns**. These are** States, Product, Quantity, **and **Sales**. The dataset is given below.

### 1. Applying AND & AVERAGEIF Functions for Multiple Criteria

Here, I will use **AND**, and **AVERAGEIF** functions for multiple criteria in Excel. Suppose, I want to find out the** average** **Sales** for the **Product: Mouse** from** the States: New York**. The steps are given below.

**Steps:**

- Firstly, you have to select a new cell
**F5**where you want to keep the result. - Secondly, you should use the formula given below in the
**F5**cell.

`=AND(B5= "New York",C5= "Mouse")`

Here, in this formula, the **AND** function will return **TRUE **if the cell value of **B5** is **“New York” **and the cell value of** C5** is **“Mouse”**.

- Subsequently, press
**ENTER**to get the result.

- After that, you have to drag the
**Fill Handle**icon to**AutoFill**the corresponding data in the rest of the cells**F6:F14**.*Or you can double-click on the***Fill Handle**icon.

Finally, you will get the** Status**. This means you will come to know whose cells fulfill those logic.

- Then, write down the following formula in the
**I10**cell.

`=AVERAGEIF(F5:F14,TRUE,E5:E14)`

Here, in this formula the **AVERAGEIF** function will do the average of the **Sales **column. Because **E5:E14** is the average range. Additionally, **F5:F14** is the criteria range and **TRUE** is the criteria.

- After that, press
**ENTER**.

Finally, you will get the **average sales** for the product **Mouse** from **New York**.

**Read More:** **How to Calculate Average If Number Matches Criteria in Excel**

### 2. Use of AVERAGEIF Function & OR Logic

Here, you may use the **OR** logic with **AVERAGE**, and **AVERAGEIF** functions to do average with multiple criteria in Excel. Suppose, I want to find out the **average Sales** for the product** Calculator **or **Mouse**. The steps are given below.

**Steps:**

- Firstly, you have to select a new cell
**F5**where you want to keep the result. - Secondly, you should use the formula given below in the
**F5**cell.

`=AVERAGE(AVERAGEIF(C5:C14,"Mouse",E5:E14),AVERAGEIF(C5:C14,"Calculator",E5:E14))`

**Formula Breakdown**

- Firstly,
**AVERAGEIF(C5:C14,”Calculator”,E5:E14)—>**here the**AVERAGEIF**function will average from the**E5:E14**data range, which will fulfill the given condition. In addition, the condition is in the**C column**whose cells value is**Calculator.****Output: $600.**

- Secondly,
**AVERAGEIF(C5:C14,”Mouse”,E5:E14)—->**again the**AVERAGEIF**function will average from the**E5:E14**data range, which will fulfill the given condition. In addition, the condition is in the**C column**whose cell value is**Mouse.****Output: $1333.**

- Lastly,
**AVERAGE($600,$1333)—>**returns**$967.**

- Subsequently, press
**ENTER**to get the result.

Finally, you will get the **average Sales** for the product** Calculator **or **Mouse**.

**Read More:** **Excel AVERAGEIF with ‘Greater Than’ and ‘Less Than’ Criteria**

### 3. Employing AVERAGEIF Function with Multiple Criteria

Here, I will employ not only the **AVERAGEIF** function but also the **AVERAGE **function for multiple criteria in Excel. Suppose, I want to find out the **average Sales** for the product** Calculator **and **Monitor**. The steps are given below.

**Steps:**

- Firstly, you have to write down the criteria in the
**G8**:**G9**cells. - Secondly, select a new cell
**H8**where you want to keep the result.*Here, you should keep***blank cells**next to the**H8**cell. Furthermore, the**blank cells**should be equal to the number of given criteria. - Thirdly, you should use the formula given below in the
**H8**cell.

`=AVERAGEIF(C5:C14,G8:G9,E5:E14)`

Here, the **AVERAGEIF** function will average from the **E5:E14 **data range, which will fulfill the given condition. Additionally, **C5:C14** is the criteria range and **G8:G9** is the criteria.

- Subsequently, press
**ENTER**to get the result.

Finally, you will get the **average Sales** individually for the product** Monitor **and **Calculator**.

At this time, I will do the average of the output.

- Now, write down the following formula in the
**H11**cell.

`=AVERAGE(H8#)`

Here, in this formula the **AVERAGE** function will do the average of the **H8** and** H9 **cells. Actually, the **Hash (#) **sign appears when there are only **two** cells in a data range.

- After that, press
**ENTER**.

Finally, you will get the **average sales** for the product **Monitor** and **Calculator**.

**Read More:** **How to Calculate Average If Cell Is Not Blank in Excel**

### 4. Employing AVERAGE & AVERAGEIF Functions

Again, I will use the **AVERAGEIF**, and the **AVERAGE** functions to find out the **average** of some values from different tables in Excel. For this, let’s have the following dataset. Which has **two **tables.

Now, suppose, I want to find out the **average Sales** for the product** Calculator **from the state of **California **and the **Mouse** from the state of **New Jersey**. Let’s find the **average of Sales** for product** Calculator **from state **California **first. The steps are given below.

**Steps:**

- Firstly, select a new cell
**D10**where you want to keep the result. - Secondly, you should use the formula given below in the
**D10**cell.

`=AVERAGEIF(C6:C9,"Calculator",D6:D9)`

Here, in this formula the **AVERAGEIF** function will do the average of the **Sales **column. Because **D6:D9** is the average range. Additionally, **C6:C9** is the criteria range and **Calculator** is the criteria.

- Thirdly, press
**ENTER**.

At this time, you will see the **average Sales** for the product** Calculator **from the state **California.**

Now, let’s find the **average sales** for product** Mouse** from the state of **New Jersey**.

- Firstly, select a new cell
**D19**where you want to keep the result. - Secondly, you should use the formula given below in the
**D19**cell.

`=AVERAGEIF(C14:C18,"Mouse",D14:D18)`

Here, in this formula the **AVERAGEIF** function will do the average of the **Sales **column. Because **D14:D18** is the average range. Additionally, **C14:C18** is the criteria range and **Mouse** is the criteria.

- Thirdly, press
**ENTER**.

Last but not least, you will see the **average sales** for product** Mouse** from the state of **New Jersey**.

Now, find the final average.

- So, write down the following formula in the
**G9**cell.

`=AVERAGE(D10,D19)`

Here, in this formula the **AVERAGE** function will do the average of the **D10** and** D19 **cells.

- After that, press
**ENTER**.

Finally, you will get the **average sales** for product** Calculator **from the state of **California **and **Mouse** from the state of **New Jersey**.

**Read More: ****How to Find Average If Values Lie Between Two Numbers in Excel**

### 5. Using AVERAGEIF Function in Array

Here, I will use not only the **AVERAGEIF** function but also the **AVERAGE** function for multiple criteria as arrays in Excel. Suppose, I want to find out the **average Sales** for the product** Calculator **and **Monitor**. The steps are given below.

**Steps:**

- Firstly, select a new cell
**D17**where you want to keep the result.*Here, you should keep***blank cells**next to the**D17***cell (horizontally). Furthermore, the***blank cells**should be equal to the number of given criteria. - Secondly, you should use the formula given below in the
**D17**cell.

`=AVERAGEIF(C5:C14,{"Monitor","Calculator"},E5:E14)`

Here, the **AVERAGEIF** function will average from the **E5:E14 **data range, which will fulfill the given condition. Additionally, **C5:C14** is the criteria range and the criteria are **Monitor** and **Calculator**.

- Subsequently, press
**ENTER**to get the result.

Finally, you will get the **average Sales** individually for the product** Monitor **and **Calculator**.

At this time, I will do the average of the output.

- Now, write down the following formula in the
**E19**cell.

`=AVERAGE(D17#)`

Here, in this formula the **AVERAGE** function will do the average of the **D17** and** E17 **cells. Actually, the **Hash (#) **sign appears when there are only **two** cells in a data range.

- After that, press
**ENTER**.

Finally, you will get the final **average of sales** for the product **Monitor** and **Calculator**.

## Use of AVERAGEIFS Function in Excel

Here, I will employ **the AVERAGEIFS function** to do the average for multiple criteria in Excel. Suppose, I want to find out the **average** **Sales** for the **Product: Mouse** from** States: California**. The steps are given below.

**Steps:**

- Firstly, select a new cell
**H10**where you want to keep the result. - Secondly, you should use the formula given below in the
**H10**cell.

`=AVERAGEIFS(E5:E14,B5:B14,"California",C5:C14,"Mouse")`

- After that, press
**ENTER**.

Finally, you will get the **average sales** for **Product: Mouse** from** States: California**.

**Formula Breakdown**

Here, the **AVERAGEIFS** function will average from the **E5:E14 **data range, which will fulfill the given condition.

- Firstly,
**B5:B14**is the 1st criteria range and**“California”**is the criteria. - Secondly,
**C5:C14**is the 2nd criteria range and**“Mouse”**is the criteria.

## Practice Section

Now, you can practice the explained method by yourself.

## Conclusion

I hope you found this article helpful. Here, I have explained **5** suitable examples about how to use an **AVERAGEIF** function with multiple criteria in Excel. You can visit our website **ExcelDemy** to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.