AVERAGEIF

AVERAGEIF(range, criteria [,average_range])

Returns the arithmetic mean of the numerical values 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

* This function is only available for backwards compatibility and should not be used.
* AVERAGEIFS was added in Excel 2007 to replace this function.
* When replacing this function with the AVERAGEIFS function, remembering to change the order of the arguments.
* 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.
* You can use the IF function to return a value based on one condition.
* You can use the AVERAGE function to return the arithmetic mean of the numerical values.
* You can use the AVERAGEIFS function to return the arithmetic mean of the numerical values that satisfies multiple conditions.
* This is similar to the COUNTIF and SUMIF functions.
* This function was added in Excel 2007.
* 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 -