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

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset of how to Use AVERAGEIF with Multiple Criteria in Excel


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

Applying AND & AVERAGEIF Functions for Multiple Criteria in Excel

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

Use of AVERAGEIF Function & OR Logic to Find Average with Multiple Criteria in Excel

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.

Employing AVERAGEIF Function with Multiple Criteria in Excel

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

Employing AVERAGE & AVERAGEIF Functions to find Average in Excel

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.

Using AVERAGEIF Function in Array to find Average in Excel

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

Use of AVERAGEIFS Function with Multiple Criteria in Excel

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 five suitable examples of 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


<< Go Back to Excel AVERAGEIF Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo