Basic Description

The Excel Averageifs function finds entries in one or more arrays, that satisfy a all of a set of supplied criteria, and returns the average (ie. the statistical mean) of the corresponding values in a further supplied array. The function is new in Excel 2007, and so is not available in earlier versions of Excel.

Syntax: AVERAGEIFS( average_range, criteria_range1, criteria1, [criteria_range2, criteria2], … )

Where the function arguments are:

average_range An array of numeric values (or cells containing numbers) for which the average is to be calculated, if the corresponding criteria_range entries satisfy all the supplied criteria.
[criteria_range2], …
Arrays of values (or ranges of cells containing values) to be tested against the respective criteria1, criteria2, …(The supplied criteria_range arrays must all have the same length)
[criteria2], …
The conditions to be tested against the values in criteria_range1, [criteria_range2], …

The supplied criteria can be either numeric values (including integers, decimals, dates, times, and logical values) (eg. 10, 01/01/2008, TRUE) or text strings (eg. “Text”, “Thursday”) – MUST be supplied in quotes or expressions (eg. “>12”, “<>0”) – MUST be supplied in quotes and can be entered into the function either directly, as values returned from other functions, or as references to cells containing values. The Excel Averageifs function is not case-sensitive. So, for example, the text strings “TEXT” and “text” will be classed as equal.


Excel Averageifs Function Examples

Averageifs Function Example-1






Averageifs Function Example-2






Averageif Function Errors

The most common errors from the Excel Averageifs function are listed in the table below :

#DIV/0! – Occurs if either none of the values in the supplied range array satisfy the supplied criteria or the values to be averaged are all non-numeric
#VALUE! – Occurs if the length of any of the supplied criteria_range arrays is not equal to the length of the average_range array.



Download Documents

Download Excel File

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>



clearPost Comment