Excel AVERAGEIFS with Multiple Criteria in Same Range

Excel is a fantastic tool to perform conditional calculations. You can average your range of datasets based on particular conditions by using the AVERAGEIFS function. In this article, I will show you how to perform the AVERAGEIFS function with multiple criteria in the same range with 4 practical examples.


Download Practice Workbook

You can download our practice workbook from here for free!


4 Examples of Excel AVERAGEIFS with Multiple Criteria in the Same Range

Say, you have a dataset of 10 product models, categories, Sales quantity, and profit for each one. Now. you want to calculate the average of the profit using multiple criteria in Excel. You can use the AVERAGEIFS function to accomplish this.

Sample Dataset to Apply AVERAGEIFS with Multiple Criteria in Same Range


1. AVERAGEIFS for Multiple Criteria with Numbers

Say, you want to average the profits for the products whose sales quantity is greater than or equal to 100 and profit is greater than or equal to 50,000. Go through the steps below to accomplish this.

πŸ“Œ Steps:

  • First and foremost, specify your conditions in cells B17 and C17 for better visualization.

Criteria to Apply AVERAGEIFS

  • At this time, click on cell D17.
  • Subsequently, insert the following formula and press the Enter key.
=AVERAGEIFS(E5:E14,D5:D14,">=100",E5:E14,">=50000")

AVERAGEIFS with Multiple Number Criteria in Same Range

Thus, you will get the average profit for only the products that have sales quantity greater than or equal to 100 and profit is greater than or equal to 50000.

Read More: How to Use AVERAGEIFS Function for Multiple Columns


2. AVERAGEIFS with Words Criteria

Now, it might occur that, you need the average profit for mobiles that have sales quantity greater than or equal to 100.

πŸ“Œ Steps:

  • At the very beginning, record your conditions in cells B17 and C17.

Criteria to Average

  • Afterward, click on cell D17 and insert the following formula.
=AVERAGEIFS(E5:E14,C5:C14,"Mobile",D5:D14,">=100")
  • Following, hit the Enter key.

AVERAGEIFS Function with Multiple Word Criteria in Same Range in Excel

As a result, you will achieve your desired result in cell D17.

Read More: AVERAGEIFS Function with β€œNot Equal to” Criteria (3 Examples)


3. Multiple Criteria with Other Cell References

Besides, you might need to calculate the average for previous conditions, but by using some cell references. Follow the steps below to do this.

πŸ“Œ Steps:

  • Initially, record your conditions in cells B17 and C17.
  • In these cells, put the condition values only.

Criteria to Average

  • At this time, click on cell D17.
  • Subsequently, insert the following formula and press the Enter key.
=AVERAGEIFS(E5:E14,C5:C14,B17,D5:D14,">="&C17)

AVERAGEIFS Function with Multiple Cell Reference Criteria in Same Range

Consequently, you get your required filtered average in cell D17.

Read More: How to Use AVERAGEIFS Between Two Values in Excel


4. Multiple Criteria with Partially Matched Words

Moreover, sometimes it might occur that you need to average for products bearing the same root name. Say, you need to average the profit of iPhones that have sales greater than or equal to 100 regardless of their models. Follow the steps below to accomplish this type of average.

πŸ“Œ Steps:

  • First, record the conditions in cells B17 and C17.

Criteria to Average

  • Afterward, click on cell D17 and insert the following formula.
=AVERAGEIFS(E5:E14,B5:B14,"iPhone*",D5:D14,">=100")
  • Subsequently, hit the Enter key.

AVERAGEIFS Function with Multiple criteria for Partially Matched Words in Same Range

As a result, you will get the required average profit for iPhones that have sales quantity greater than or equal to 100.

Read More: AVERAGEIFS for Multiple Criteria in Different Columns in Excel


Excel AVERAGE with Multiple OR Criteria in the Same Range

Furthermore, you might need to average with multiple OR criteria in the same range sometimes. You can do this by inserting OR function inside the AVERAGE function with an IF function. Say, you need to average the profit for Laptops or Mobiles that have sales quantity greater than or equal to 100. Go through the steps below to do this.

πŸ“Œ Steps:

  • Like previous ways, record your conditions first in cells B17 and C17.

Criteria to Average with OR Criteria

  • Afterward, click on cell D17 and insert the following formula.
=AVERAGE(IF((C5:C14={"Mobile","Laptop"})*(D5:D14>=100),E5:E14))
  • Subsequently, press the Ctrl + Shift + Enter key.

AVERAGE Function with Multiple OR Criteria

As a result, you will get the desired average result for your OR conditions.


πŸ’¬ Things to Remember

  • If no cell matches your given criteria in the AVERAGEIFS function, the function will return the #DIV0! error.
  • You need to put your non-numeric criteria inside double quotes(β€œ ”).
  • The criteria ranges and average range has to possess the same number of rows and columns.

Conclusion

So, in this article, I have shown you 4 practical examples of AVERAGEIFS with multiple criteria in the same range. I suggest you read the full article carefully and apply the knowledge to your needs. You can also download our free workbook to practice. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.

And, visit ExcelDemy to learn more things about Excel! Have a nice day!

Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo