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) = 30.0	Jan	20
2	=AVERAGEIF(B1:B5, "=Jan", C1:C5) = 30.0	Feb	30
3	=AVERAGEIF(C1:C5, "<0") = #DIV/0!	Jan	40
4	=AVERAGEIF(C1:C5, ">40") = 70.0	Feb	60
5	=AVERAGEIF(C1:C5, "<>0") = 46.0	Mar	80
6	=AVERAGEIF(C1:C5, "=20") = 20.0
7	=AVERAGEIF(C1:C5, ">" & C3) = 70.0	01 Jan 2025
8	=AVERAGE(C1:C6) = 46.0	02 Jan 2025
9	=AVERAGEIF(B1:B6, "*", C1:C6) = 46.0	03 Jan 2025
10	=AVERAGEIF(B1:B5, "B", C1:C5) = 45.0	04 Jan 2025
11	=AVERAGEIF(B1:B5, "b", C1:C5) = 45.0	05 Jan 2025
12	=AVERAGEIF(B1:B6, "???", C1:C6) = 46.0
13	=AVERAGEIF(B1:B6, "?e*", C1:C6) = 45.0
14	=AVERAGEIF(B7:B11, ">02/01/2025", C1:C5) = 60.0
15	=AVERAGEIF(B7:B11, ">02 Jan 2025", C1:C5) = 60.0
16	=AVERAGEIF(B7:B11, ">"&DATE(2025, 1, 2), C1:C5) = 60.0
17	=AVERAGEIF(B7:B11, "<"&TODAY(), C1:C5) = 46.0

1 - What is the average of the values in the range "C1:C5" that contain the value "Jan" in the range "B1:B5". 60/2
2 - What is the average of the values in the range "C1:C5" that contain the value "Jan" in the range "B1:B5". 60/2
3 - What is the average of the values in the range "C1:C5" that contain a negative number. 0/5=#DIV/0!
4 - What is the average of the values in the range "C1:C5" that contain a number greater than 40. 140/2
5 - What is the average of the values in the range "C1:C5" that contain a non zero value. 230/5
6 - What is the average of the values in the range "C1:C5" that contain the number 20.
7 - What is the average of the values in the range "C1:C5" that contains a number greater than 40. 140/2
8 - What is the average of the values in the range "C1:C6" that contain numerical values. 230/5
9 - What is the average of the values in the range "C1:C6" that contain text in the range "B1:B6". 230/5
10 - What is the average of the values in the range "C1:C6" that contain the letter "B" in the range "B1:B6". This is not case sensitive. 90/2
11 - What is the average of the values in the range "C1:C6" that contain the letter "b" in the range "B1:B6". This is not case sensitive. 90/2
12 - What is the average of the values in the range "C1:C6" that contain only three letters in the range "B1:B6". 230/5
13 - What is the average of the values in the range "C1:C6" that have the letter "e" as their second character in the range "B1:B6". 90/2
14 - What is the average of the values in the range "C1:C5" that contain a date greater than "02/01/2025" in the range "B7:B11". 180/3
15 - What is the average of the values in the range "C1:C5" that contain a date greater than "2 Jan 2025" in the range "B7:B11". 180/3
16 - What is the average of the values in the range "C1:C5" that contain a date greater than "2 Jan 2025" in the range "B7:B11". 180/3
17 - What is the average of the values in the range "C1:C5" that contain a date greater than today in the range "B7:B11". 0/5