How to Use Excel AVERAGEIF with Multiple Criteria (5 Examples)

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

• After that, press ENTER.

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

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.

Related Articles

• How to Find Average If Cell Contains Text in Excel
• How to Calculate Average If Cell Is Not Blank in Excel