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

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

ShowExample 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