AVERAGEIF

AVERAGEIF(range, criteria [,average_range])

Returns the arithmetic mean of the values in a list, table or cell 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 use a tilde prefix (~?), (~*) 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 zero are included.* Arguments that are logical values or text are excluded.* If any cell references are empty, then these are excluded.* If any cell references contain an error, then that error is returned.* The "criteria" can contain wildcard characters.* This is similar to the COUNTIF and SUMIF functions.* For the Microsoft documentation refer to support.microsoft.com* For the Google documentation refer to support.google.com

 A B C 1 =AVERAGEIF(B1:B5, "Jan", C1:C5) = 25.0 Jan 30 2 =AVERAGEIF(B1:B5, "=Jan", C1:C5) = 25.0 Feb 40 3 =AVERAGEIF(B1:B5, "=Feb", C1:C5) = 50.0 Jan 20 4 =AVERAGEIF(C1:C5, "<>20") = 52.5 Feb 60 5 =AVERAGEIF(C1:C5, "=20", C1:C5) = 20.0 Mar 80 6 =AVERAGEIF(C1:C5, ">" & C3, C1:C5) = 52.5 7 =AVERAGEIF(B1:C8, ", ") = #DIV/0! 8 =AVERAGEIF(B1:C8, ", B*") = #DIV/0! 9 =AVERAGEIF(B1:C8, ", b*") = #DIV/0! 10 =AVERAGEIF(B1:C8, "???") = #DIV/0! 11 =AVERAGEIF(B1:C8, "?e, ") = #DIV/0! 12 =AVERAGEIF(B1:C8, "Y") = #DIV/0! 13 =AVERAGEIF(B9:C9, "<") = #DIV/0! 14 =AVERAGEIF(B9:C9, "<") = #DIV/0! 15 =AVERAGEIF(B1:C8, "Y") = #DIV/0!

 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 - If the "sum_range" is left blank, then the "range" of cells is summed.7 - 8 - 9 - 10 - 11 - 12 - 13 - 14 - 15 -