# Excel AVERAGEIFS with Multiple Criteria in Same Range

Get FREE Advanced Excel Exercises with Solutions!

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.

## Excel AVERAGEIFS with Multiple Criteria in the Same Range: 4 Examples

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. ### 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. • 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")` 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 Excel AVERAGEIFS Function with Multiple Criteria

### 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. • Afterward, click on cell D17 and insert the following formula.
`=AVERAGEIFS(E5:E14,C5:C14,"Mobile",D5:D14,">=100")`
• Following, hit the Enter key. As a result, you will achieve your desired result in cell D17.

### 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. • 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)` Consequently, you get your required filtered average in cell D17.

### 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. • Afterward, click on cell D17 and insert the following formula.
`=AVERAGEIFS(E5:E14,B5:B14,"iPhone*",D5:D14,">=100")`
• Subsequently, hit the Enter key. As a result, you will get the required average profit for iPhones that have sales quantity greater than or equal to 100.

## 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 the OR function inside the AVERAGE function with the 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. • 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. 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.

Thank you!

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

1. Reply Hello there!

Need some help with a somewhat complex formula (I think?). I’m trying to find the average of two separate criteria from the same column in one sheet while also making it date specific using the EOMONTH function from another sheet where the date is defined in it’s cell.

Just FYI – This below formula works as an array perfectly however when I attempt to borrow/copy the EOMONTH portion from another formula that works (further down below) it it no longer works. I believe I just simply do not understand the right combination or order to combne everything together.

=AVERAGE(IF(ISNUMBER(MATCH(‘ONLINE MASTER’!F2:F10000,{“TELEPHONE”,”COMPUTER”},0)),’ONLINE MASTER’!D2:D10000))

More Info: I am plugging these formulas in on one sheet but the bulk of the criteria is found on one sheet while the date specific info lives on the second sheet. Here is all the sheet/column info for both and what each represents:

Sheet Names
ONLINE MASTER = Sheet 1
2022 = Sheet 2

Column’s in Sheet 1 “ONLINE MASTER”:

Column A = Where all the dates are (data exists in cells A2-A10000)
Column D = The numbers I need to average (data exists in cells D2-D10000)
Column F = Different sets of category items (i.e. PHONE, COMPUTER, STAPLER, SCISSORS)(data exists in cells F2-F10000)

Column’s in Sheet 2 “2022” (where the formula is intended to go):

Column A = data where all preset dates are in Month-Year format (i.e. Oct-22)

Another formula on sheet 2: “2022” where the EOMONTH formula works is a follows:

=IFERROR(ROUND(AVERAGEIFS(‘ONLINE MASTER’!\$D\$2:\$D\$10000, ‘ONLINE MASTER’!F2:F10000,”COMPUTER”,’ONLINE MASTER’!\$A\$2:\$A\$10000,”>=”&EOMONTH(\$A\$63,-1)+1,’ONLINE MASTER’!\$A\$2:\$A\$10000,”<="&EOMONTH(\$A\$63,0)),2),0)

In the above formula A63 represents October 2022 and the formula calculates the average of all computers sold in the month of October based on their respective prices.

All months in the year also live defined in column A and I'm sure if you could please help me with this one formula it will unlock all the other formulas as well.

• Reply Hello, CRYSTAL!

As far as I understood your question, you want to apply date criteria and multiple Category criteria in your formula to find the average of a different column.
You can accomplish this using the nested FILTER function along with the AVERAGE, EOMONTH, and DAY functions and you have to use the plus sign between the categories to enable the OR criteria. The final formula would look like this.

`=AVERAGE((FILTER('ONLINE MASTER'!\$D\$2:\$D\$1000,(('ONLINE MASTER'!\$F\$2:\$F\$1000="COMPUTER")+('ONLINE MASTER'!\$F\$2:\$F\$1000="TELEPHONE"))*('ONLINE MASTER'!\$A\$2:\$A\$1000>=('2022'!\$A63-DAY('2022'!\$A63)+1))*('ONLINE MASTER'!\$A\$2:\$A\$1000<=EOMONTH(('2022'!\$A63-DAY('2022'!\$A63)+1),0)))))`

I hope this solves your problem.

With Regards,
Md. Tanjim Reza Tanim

2. Reply I found that you can use a=verage(averageif(;;);averageif(;;)) or you can use a=verage(averageifs(;;;;);averageifs
(;;;;))

• Reply Mohammad Shah Miran Mar 19, 2023 at 12:07 PM

Thank you, Hossam for your query. I am not sure whether you alter AVERAGEIF with AVERAGEIFS. If so, there should be a difference between them as AVERAGEIF deals with single criterion whereas AVERAGEIFS deals with multiple criteria. However, you can use the AVERAGEIFS function for a single criterion by specifying only one criterion range and one criterion. For example, you have a range of cells A1:A10 that contains numbers and you want to calculate the average of the cells that are greater than or equal to 5. For accomplish your task, you can use the AVERAGEIFS function like this:
`=AVERAGEIFS(A1:A10, A1:A10, ">=5")`
Further if you have any query, please let me know. Thank you.

3. Reply I’ve looked at several sites and this is where I found what I needed. I don’t know if it’s the wording I’ve used but I could not find this last part anywhere else ‘Excel AVERAGE with Multiple OR Criteria in the Same Range’.
Thank you.

• Reply Md. Abdur Rahim Rasel Jul 9, 2023 at 11:46 AM

Hello ZAC!
Thanks for sharing your problem with us!
You’ve used the wording but could not find this last part anywhere else ‘Excel AVERAGE with Multiple OR Criteria in the Same Range’.
The formula returns #DIV/0! error because the matching values are different from the data table. You can fix the issue using the following formula with a partial match.

``=AVERAGE((C5:C14={"*Mob*","*Lap*"})*(D5:D14>=100),E5:E14)`` Excel AVERAGEIFS with Multiple Criteria in Same Range
[email protected]
Regards
Md. Abdur Rahim Rasel
Exceldemy Team Advanced Excel Exercises with Solutions PDF  