AVERAGEIF

AVERAGEIF(range, criteria [,average_range])

Returns the arithmetic mean of the numerical values that satisfies one condition.

rangeThe range of cells you want evaluated.
criteriaThe 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 uses the actual cell values and not the displayed formatted values.
* This function is only available for backwards compatibility and should not be used.
* If you are replacing this function with the AVERAGEIFS function, remember to change the order of the arguments.
* Arguments that are cell ranges containing zeros are included.
* Arguments that are cell ranges containing dates are included and the date serial number is used.
* Arguments that are cell ranges containing hidden cells are included.
* Arguments that are cell ranges containing error values will be returned.
* Arguments that are cell ranges containing logical values are excluded.
* Arguments that are cell ranges containing text are excluded.
* Arguments that are cell ranges containing empty cells are excluded.
* This function is not case sensitive when matching text strings.
* This function supports wildcards (? is a single character and * is multiple characters).
* To include the actual wildcard characters use a tilde prefix (~?), (~*) and (~~).
* 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.
* 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

 ABC
1=AVERAGEIF(B1:B5, "Jan", C1:C5) = 30.0Jan20
2=AVERAGEIF(B1:B5, "=Jan", C1:C5) = 30.0Feb30
3=AVERAGEIF(C1:C5, "<0") = #DIV/0!Jan40
4=AVERAGEIF(C1:C5, ">40") = 70.0Feb60
5=AVERAGEIF(C1:C5, "<>0") = 46.0Mar80
6=AVERAGEIF(C1:C5, "=20") = 20.0  
7=AVERAGEIF(C1:C5, ">" & C3) = 70.001 Jan 2025 
8=AVERAGE(C1:C6) = 46.002 Jan 2025 
9=AVERAGEIF(B1:B6, "*", C1:C6) = 46.003 Jan 2025 
10=AVERAGEIF(B1:B5, "*B*", C1:C5) = 45.004 Jan 2025 
11=AVERAGEIF(B1:B5, "*b*", C1:C5) = 45.005 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
© 2025 Better Solutions Limited. All Rights Reserved. © 2025 Better Solutions Limited Top