The Excel AVERAGEIFS Function

Related Functions:
AVERAGEIF
SUMIF
SUMIFS

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.

The syntax of the Averageifs function is :

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_range1
[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)

criteria1,
[criteria2], ...
- The conditions to be tested against the values in criteria_range1, [criteria_range2], ...

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:

- 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

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.


  A B C D
1 Quarter Area Sales Rep. Sales
2 1 North Jeff $223,000
3 1 North Chris $125,000
4 1 South Carol $456,000
5 2 North Jeff $322,000
6 2 North Chris $340,000
7 2 South Carol $198,000
8 3 North Jeff $310,000
9 3 North Chris $250,000
10 3 South Carol $460,000
11 4 North Jeff $261,000
12 4 North Chris $389,000
13 4 South Carol $305,000

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:

- The value in column A is equal to 1
and
- The entry in column B is equal to "North"

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:

- The value in column A is greater than 2
and
- The entry in column C is equal to "Jeff"

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).


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


Averageif Function Errors

The most common errors from the Excel Averageifs 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
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.
Return to the Excel Statistical Functions Page

Return to the List of All Built-In Excel Functions

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2013 ExcelFunctions.net