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.

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.

**Table of Contents**hide

## 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 that 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. Using 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**.

## 3. Employing the 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:** **Excel AVERAGEIF with ‘Greater Than’ and ‘Less Than’ Criteria**

## 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**.

**Read More:** **Excel AVERAGEIF Function for Values Greater Than 0**

## 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.

**Download Practice Workbook**

You can download the practice workbook from here:

## 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. Please, drop comments, suggestions, or queries if you have any in the comment section below.