The Excel AVERAGEIF Function

Related Functions:
AVERAGEIFS
SUMIF
SUMIFS

Basic Description

The Excel AVERAGEIF function finds the values in a supplied array that satisfy a specified criteria, and returns the average (ie. the statistical mean) of the corresponding values in a second supplied array.

The function is new in Excel 2007, and so is not available in earlier versions of Excel.

The syntax of the Averageif function is:

AVERAGEIF( range, criteria, [average_range] )

Where the function arguments are:

range-An array of values (or range of cells containing values) to be tested against the supplied criteria.
criteria-The condition to be tested against each of the values in range.
[average_range]-

An optional array of numeric values (or cells containing numbers) for which the average is to be calculated, if the corresponding range value satisfies the supplied criteria.

If the [average_range] argument is omitted, the average is calculated for values in the initial supplied range.

Wildcards

You can also use the following wildcards in text-related criteria:

?    -    matches any single character
*    -    matches any sequence of characters

if you do actually want to find the ? or * character, type the ~ symbol before this character in your search.

eg. the condition "A*e" will match all cells containing a text string beginning with "A" and ending in "e".

The supplied criteria can be either:

oror

Note also that the Excel Averageif function is not case-sensitive. So, for example, the text strings "TEXT" and "text" will be classed as equal.


Excel Averageif Function Examples

The following spreadsheets shows several examples of the Excel Averageif function.

For each call to the Excel Averageif function the range argument (to be tested against the criteria) is the cell range A1 - A14 and the [average_range] argument (containing the values to be averaged) is the cell range B1 - B14.

The averageif function is entered into cells A16 - A20 (highlighted in yellow):

 Formulas:
 ABC
1Monday500 
2Tuesday50 
3Thursday100 
4Friday100 
5Thursday200 
65300 
72200 
83100 
9450 
105100 
11150 
12TRUE200 
13TRUE250 
14FALSE50 
15   
16=AVERAGEIF( A1:A14, "Thursday", B1:B14 )
17=AVERAGEIF( A1:A14, 5, B1:B14 )
18=AVERAGEIF( A1:A14, ">2", B1:B14 )
19=AVERAGEIF( A1:A14, TRUE, B1:B14 )
20=AVERAGEIF( A1:A14, "<>TRUE", B1:B14 )
 Results:
 AB
1Monday500
2Tuesday50
3Thursday100
4Friday100
5Thursday200
65300
72200
83100
9450
105100
11150
12TRUE200
13TRUE250
14FALSE50
15  
16150 
17200 
18137.5 
19225 
20150 

Note that, in cells A16, A18 and A20 of the above 'Formulas' spreadsheet, the text value "Thursday" and the expressions ">2" and "<>TRUE" are encased in quotes.


Further examples of the Excel Averageif function can be found on the Microsoft Office website.


Averageif Function Errors

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

Common Errors
#DIV/0!-

Occurs if either:

  • None of the values in the supplied range array satisfy the supplied criteria;
  • The values to be averaged are all non-numeric.
#VALUE!-Occurs if the criteria argument is a text string with length greater than 255 characters.