Today I will be showing how you can use the **AVERAGEIFS** function of Excel to calculate some averages while maintaining one or more criteria in Excel.

AVERAGEIFS Function of Excel (Quick View)

**Table of Contents**hide

**Download Practice Workbook**

**Excel AVERAGEIFS Function: Syntax and Argument**

**Summary**

- The
**AVERAGEIFS**function returns the average of the cells of an array that satisfy one or more given criteria. The criteria can be of the same array or a different array. - Available from Excel 2007.

**Syntax**

The Syntax of the **AVERAGEIFS** function is:

`=AVERAGEIFS(average_range,criteria_range1,criteria1,...)`

**Argument**

Argument |
Required or Optional |
Value |

average_range | Required | The array of cells whose average is to be determined. |

criteria_range1 | Required | The array of cells that needs to satisfy the first criteria. |

criteria1 | Required | The first criteria. |

criteria_range2 | Optional | The array of cells that needs to satisfy the second criteria. |

criteria2 | Optional | The second criteria. |

… | … | … |

… | … | … |

**Notes:**

- Only one criterion and one range of cells where the criteria will be applied (
**criteria_range**) is compulsory. But you can use as many criteria as you wish. - The
**criteria**and the**criteria_range**must come in pairs. That means if you input**criteria_range 3**, you must input**criteria3**. - The lengths of the
**average_range**and all**criteria_ranges**must be equal. Otherwise, Excel will raise**#VALUE!**Error. - While calculating the average, Excel will count only those cells that satisfy all the criteria.

**Return Value**

Returns the average of the cells of an array that satisfy one or more given criteria.

**Special Notes**

- When the criterion denotes equal to some value or cell reference, just put the value or the cell reference in place of the criteria.

Like this:

`=AVERAGEIFS(C5:C9,C5:C9,1)`

OR

`=AVERAGEIFS(C5:C9,C5:C9,"Won")`

OR

`=AVERAGEIFS(C5:C9,C5:C9,A2)`

- When the criterion denotes greater than or less than some value, enclose the criteria within an
**apostrophe (“”)**

Like this:

`=AVERAGEIFS(C5:C9,C5:C9,">1")`

- When the criterion denotes greater than or less than some cell reference, enclose only the greater than or the less than symbol within an
**apostrophe (“”)**and then join the cell reference by an**ampersand (&)**symbol.

Like this:

`=AVERAGEIFS(C5:C9,C5:C9,">"&A2)`

- You can have partial matches too within the
**AVERAGEIFS**function.

For matching any one character in a specific place, use “**?”.**

For example, “**?end”** will match “**bend”**, “**send”** but not “**spend” **or** “end”.**

And for matching any number of characters including zero, use “***”**.

For example, “***end”** will match “**end”**, “**bend”**, “**send”**, “**spend”** all.

So the **AVERAGEIFS** formula will look like:

`=AVERAGEIFS(C5:C9,C5:C9,"?end")`

OR

`=AVERAGEIFS(C5:C9,C5:C9,"*end")`

- If any cell within the
**average_range**contains a text value other than a number,**AVERAGEIFS**will not count that even if it satisfies all the criteria. Because it is only possible to calculate the average of some numbers, not any text.

**Excel AVERAGEIFS Function: 4 Examples**

### 1. Counting Average with a Single Criteria Equal to a Value

Look at the data set below. We have the records of the last two months of a soccer player named Alfred Moyes.

Let’s try to find out his average of goals in the winning matches.

The formula will be:

`=AVERAGEIFS(C5:C22,E5:E22,"Won")`

We see, his average number of goals in the winning matches is 2.09. That is quite good, isn’t it?

Formula |
Output |
Explanation |

=AVERAGEIFS(C5:C22,E5:E22,”Won”) | 2.09 | Calculates the average of only those cells in the array C5 to C22 whose corresponding cells in the array E5 to E22 contain “Won”. |

Now if you understand this, can you tell me the formula to determine his goals average in away matches?

Yes. You are right. The formula will be:

`=AVERAGEIFS(C5:C22,F5:F22,"Away")`

**2. Counting Average with a Single Criteria Greater than a Value**

His statistics suggest that he did play well, but not played well in all the matches.

Now let’s try to find out his average number of goals in the matches when he had at least one assist.

The formula will be:

`=AVERAGEIFS(C5:C22,D5:D22,">=1")`

You see, in the matches with assists, his average is 1.80. That is quite good also.

Formula |
Output |
Explanation |

=AVERAGEIFS(C5:C22,D5:D22,”>=1″) | 1.80 | Calculates the average of only those cells in the array C5 to C22 whose corresponding cells in the array D5 to D22 contain anything greater than or equal to 1. |

Now can you tell me the formula to determine his goals average in the matches when he scored at least one goal?

Yes. You are right. The formula will be:

`=AVERAGEIFS(C5:C22,C5:C22,">=1")`

**3. Counting Average with Multiple Criteria**

This time we will find out his goals average in the home matches, where he scored at least one goal.

The formula will be:

`=AVERAGEIFS(C5:C22,C5:C22,">=1",F5:F22,"Home")`

See, this time his average is 2.33.

Formula |
Output |
Explanation |

=AVERAGEIFS(C5:C22,C5:C22,”>=1″,F5:F22,”Home”) | 2.33 | Calculates the average of only those cells in the array C5 to C22 that contain anything greater than or equal to 1 and whose corresponding cells in the array F5 to F22 contain “Home”. |

Now, if you understand this, can you tell me the formula to determine his goals average in the matches when he scored both goals and assists?

Quite easy. The formula will be:

`=AVERAGEIFS(C5:C22,C5:C22,">=1",D5:D22,">=1")`

This time his average is, even more, 2.57 goals per match.

**4. Counting Average with Partial Match**

Finally, we will count the average with a partial match.

You see, there are two Koreas on the opponent list. North and South Korea.

Let’s try to find out his average number of goals against both the Koreas.

The formula will be:

`=AVERAGEIFS(C5:C22,B5:B22,"*Korea")`

See, his average against Korea is 2.00

Formula |
Output |
Explanation |

=AVERAGEIFS(C5:C22,B5:B22,”*Korea”) | 2.00 | Calculates the average of only those cells in the array C5 to C22 whose corresponding cells in the array B5 to B22 contain anything having “Korea” at the end. |

**Note:** Here we have used “***Korea”** because we wanted to match any number of characters before the word “**Korea”**.

You can accomplish it using “**???th Korea”** too.

Remember, “**?”** matches any one character only.

If you want to know more about the **wildcard** characters, you can visit this link.

**Common Errors with Excel AVERAGEIFS Function**

Error |
When They Show |

#DIV/0! | Shows when no value in the average_match matches all criteria. |

#VALUE! | This shows when the lengths of all the arrays are not the same. |

**Conclusion**

Using these methods you can use the **AVERAGEIFS** function of Excel to calculate the average of some values that maintain one or more criteria. Do you have any questions? Feel free to ask us.

## Further Readings

- How to use AVERAGE function in Excel (5 Examples)
- How to calculate Average, Median, & Mode in Excel
- How to Use SMALL Function in Excel (4 Common Examples)
- How to Use MIN Function in Excel (5 Relevant Examples)
- How to use MAX function in Excel (6 Examples)
- How to Use LARGE Function in Excel (6 Easy Examples)
- How to Use COUNTIF Function in Excel (10 Suitable Applications)
- How to Use COUNT Function in Excel (With 5 Examples)
- The Different Ways of Counting in Excel