Excel - Microsoft Excel AVERAGEIFS Function

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. 

The syntax of the Averageifs function is : 

AVERAGEIFS( average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... ) 

Where the function arguments are: 

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: 

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

The spreadsheet below shows the quarterly sales figures for 3 sales representatives. 

The Averageifs function can be used to find average sales figures for any combination of quarter, area and sales rep. 

This is shown in the examples below. 

Example 1

To find the average quarterly sale, per person, in the North area during quarter 1: 

=AVERAGEIFS( D2:D13, A2:A13, 1, B2:B13, "North" ) 

which gives the result $174,000. 

In this example, the Excel Averageifs function identifies rows where: 

and calculates the average of the corresponding values from column D. 

ie. this formula finds the average of the values $223,000 and $125,000 (from cells D2 and D3). 

Example 2

Again, using the data spreadsheet above, we can also use the Averageifs function to find the average sales figure for "Jeff", during quarters 3 and 4: 

=AVERAGEIFS( D2:D13, A2:A13, ">2", C2:C13, "Jeff" ) 

This formula returns the result $285,500. 

In this example, the Excel Averageifs function identifies rows where: 

and calculates the average of the corresponding values in column D. 

ie. this formula finds the average of the values $310,000 and $261,000 (from cells D8 and D11). 

Averageif Function Errors

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

Common Errors