Excel AVERAGEIF Function

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

* Added in Excel 2007.
* This function includes hidden rows.
* This function uses the actual cell values and not the displayed formatted values.
* 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 ignored.
* The "criteria" can contain wildcard characters (?) for a single character and (*) for any sequence of characters. To include these characters prefix them with a tilda (~?) and (~*).
* You can use the AVERAGE function if you want to return the arithmetic mean with no conditions.
* You can use the AVERAGEA function if you want to include logical values and text in the denominator.
* You can use the AVERAGEIFS function if you want to satisfy multiple conditions.
* If you want to use a VBA function instead you can use the User Defined Function
* This is similar to the COUNTIF function.
* This is similar to the SUMIF function.
* For the Microsoft documentation refer to support.office.com.

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

Expand ...

1 - What is the average of all the values in the range "C1:C5" which have 14,000 in the range "B1:B5".
2 - This is the same as 1 except the "criteria" uses a cell reference.
3 - This is the same as 1 except the "criteria" does not include an equal sign.
4 - This is the same as 1 except the "criteria" is not contained in speech marks.
5 - 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)
6 - This is the same as 5 except the "criteria" uses a cell reference.
7 - If the "sum_range" is left blank, then the "range" of cells is summed.
8 - 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".
9 - 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".
10 - 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".
11 - 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".

Top