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