### AVERAGEIF(range, criteria [,average_range])

Returns the arithmetic mean of all the numbers in a range that satisfies one condition.

 range The range of cells you want evaluated. criteria The expression that contains the criteria. average_range (Optional) The actual cells to average.

#### Remarks

 * AVERAGEIFS was added in Excel 2007 to replace this function.* This function includes hidden cells.* This function is not case sensitive when matching text strings.* This function supports wildcards (? and *).* Wildcard character: ? = a single character.* Wildcard character: * = multiple characters.* To include the actual wildcard characters prefix them with a tilde (~?) and (~*).* This function uses the actual cell values and not the displayed formatted values.* If "average_range" is left blank, then the cells in the "range" are used for both the criteria and the average.* If no cells in "average_range" meet the criteria, then #DIV/0! is returned* Arguments that are logical values are excluded.* Arguments that are zero are included.* Arguments that are text are excluded.* If any cell references contain an error, then that error is returned.* If any cell references are empty, then these are excluded.* The "criteria" can contain wildcard characters.* You can use the AVERAGE function to return the arithmetic mean with no conditions.* You can use the AVERAGEA function to include logical values and text in the denominator.* You can use the AVERAGEIFS function to return the arithmetic mean of all the numbers in a range that satisfies multiple conditions.* You can use the AVERAGEIFSVISIBLE - User Defined Function to exclude hidden cells.* This is similar to the COUNTIF function.* This is similar to the SUMIF function.* For the Microsoft documentation refer to support.microsoft.com

 A B C D E 1 =AVERAGEIF(B1:B5,">28000") = 36000 7000 200 14000 one 2 =AVERAGEIF(B1:B5,"=14000",C1:C5) = 400 14000 400 >14000 two 3 =AVERAGEIF(B1:B5,D1,C1:C5) = 400 21000 600 three 4 =AVERAGEIF(B1:B5,"14000",C1:C5) = 400 28000 800 four 5 =AVERAGEIF(B1:B5,14000,C1:C5) = 400 36000 1000 five 6 =AVERAGEIF(B1:B5,">14000",C1:C5) = 800 7 =AVERAGEIF(B1:B5,D2,C1:C5) = 800 8 =AVERAGEIF(B1:B5,D2) = 28333 9 =AVERAGEIF(B1:B4,"="&SUM(B1:B4)/10) = 7000 10 =AVERAGEIF(B1:B5,">"&AVERAGE(B1:B5)) = 32000 11 =AVERAGE(IF(ISERROR(SEARCH("o",E1:E5))=FALSE,C1:C5,0)) = 280 12 =AVERAGE(IF(ISERROR(SEARCH("h",E1:E5))=FALSE,C1:C5,0)) = 120 13 =AVERAGEIF(B1:B4,">16000",C1:C2) = 700

 1 - What is the average of all the values in the range "B1:B5" which are greater than 28,000.2 - What is the average of all the values in the range "C1:C5" which have 14,000 in the range "B1:B5".3 - This is the same as 1 except the "criteria" uses a cell reference.4 - This is the same as 1 except the "criteria" does not include an equal sign.5 - This is the same as 1 except the "criteria" is not contained in speech marks.6 - What is the average of all the values in the range "C1:C5" which have more than 14,000 in the range "B1:B5". (600 + 800 + 1000)7 - This is the same as 5 except the "criteria" uses a cell reference.8 - If the "sum_range" is left blank, then the "range" of cells is summed.9 - What is the average of all the numbers in the range "B1:B4" that have (7000+14000+21000+28000)/10 in the range "B1:B4".10 - What is the average of all the numbers in the range "B1:B5" that have a value greater than the average of the values in the range "B1:B5".11 - Array Formula. What is the average of all the numbers in the range "C1:C5" that have the letter "o" in the range "E1:E5".12 - Array Formula. What is the average of all the numbers in the range "C1:C5" that have the letter "h" in the range "E1:E5".