AVERAGEIFS function

Returns the average (arithmetic mean) of all cells that meet multiple criteria.

Syntax

AVERAGEIFS (average_range,criteria_range1,criteria1, criteria_range2,criteria2… )

Argument

Description

Remarks

average_range

One or more cells to average.

  • Cells in average_range must contain numbers, name, array, or cell reference that contain numbers.

  • If average_range contains blank cells or cells containing text values, AVERAGEIFS returns the #DIV0! error value.

  • Cells in average_range that contain TRUE evaluate as 1; cells in range that contain FALSE evaluate as 0 (zero).

  • Each cell in average_range is used in the average calculation only if all of the corresponding criteria specified are true for that cell.

  • If cells in average_range cannot be translated into numbers, AVERAGEIFS returns the #DIV0! error value.

criteria_range1, criteria_range2,...

1 to 127 ranges in which to evaluate the associated criteria.

  • If a cell in criteria_range is empty, AVERAGEIFS treats it as a 0 (zero) value.

  • Unlike the range and criteria arguments in the AVERAGEIF function, in AVERAGEIFS each criteria_range must be the same size and shape as average_range.

criteria1, criteria2,...

1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be averaged. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.

  • If there are no cells that meet all the criteria, AVERAGEIFS returns the #DIV/0! error value.

  • You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Example 1: Average student grades

To make the following example easier to understand, you can copy the data to a blank sheet and then enter the function underneath the data. Do not select the row or column headings (1, 2, 3... A, B, C...) when you copy the sample data to a blank sheet.

Student

First Quiz Grade

Second Quiz Grade

Final Exam Grade

Emilio

75

85

87

Julie

94

80

88

Hans

86

93

Incomplete

Frederique

Incomplete

75

75

Formula

Description (Result)

 

 

=AVERAGEIFS (B2:B5,B2:B5,">70" ,B2:B5,"<90" )

Average for all students' first quiz grades that are between 70 and 90 (80.5)

=AVERAGEIFS (C2:C5,C2:C5,">95")

Average for all students' second quiz grades that are greater than 95, but none exist (#DIV/0!)

=AVERAGEIFS (D2:D5,D2:D5,"<>Incomplete" ,D2:D5,">80" )

Averages for all students' first quiz grades that are above 80 and not marked "Incomplete" (87.5)

Example 2: Average real estate prices

To make the following example easier to understand, you can copy the data to a blank sheet and then enter the function underneath the data. Do not select the row or column headings (1, 2, 3... A, B, C...) when you copy the sample data to a blank sheet.

Type of house for sale

Price

Town

Number of Bedrooms

Garage?

Rambler

230000

Issaquah

3

No

Bungalow

197000

Bellevue

2

Yes

Cape Cod

345678

Bellevue

4

Yes

Split Level

321900

Issaquah

2

Yes

Tudor

450000

Bellevue

5

Yes

Colonial

395000

Bellevue

4

No

Formula

Description (Result)

 

 

 

=AVERAGEIFS (B2:B7,C2:C7,"Bellevue" ,D2:D7,">2",E2:E7,"Yes" )

Average price of a house in Bellevue that has at least 3 bedrooms and a garage (397839)

=AVERAGEIFS (B2:B7,C2:C7,"Issaquah",D2:D7 ,"<=3",E2:E7,"No" )

Average price of a house in Issaquah that has up to 3 bedrooms and no garage (230000)

See also

IF function

AVERAGEIF function

AVERAGEA function

AVERAGE function

List of all functions (by category)

Applies To: Excel for Mac 2011



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources