# AVERAGEIFS

AVERAGEIFS(average_range, criteria_range1, criteria1 [,criteria_range2, criteria2] [..])

Returns the arithmetic mean of the values in a list, table or cell range that satisfies multiple conditions.

 average_range The actual cells to average. criteria_range1 The range of cells you want evaluated. criteria1 The expression that contains the criteria. criteria_range2 (Optional) The range of cells you want evaluated. criteria2 (Optional) The expression that contains the criteria.

 REMARKS
 * This function was added in Excel 2007 to replace the AVERAGEIF function.* For an illustrated example refer to the page under Advanced Functions* 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.* This function can handle full column references (\$A:\$A) very efficiently because it ignores empty cells.* All the "criteria_range" ranges must be the same size and shape as "criteria_range1"* If "average_range" is blank or a text value, then #DIV/0! is returned.* If "average_range" contains any cells that are not numbers, then #DIV/0! is returned.* If "average_range" contains any empty cells, these are considered equivalent to a 0 value.* If "criteria_range" contains any empty cells, then these are excluded.* The "criteria" can contain wildcard characters.* If there are no cells that meet the criteria, then #DIV/0! is returned.* Arguments that are zero are included.* Arguments that are logical values or text are excluded.* You can have up to 127 different criteria.* You can use the AVERAGE function to return the arithmetic mean with no conditions.* You can use the AVERAGEA function to include logical values and text.* You can use the AVERAGEVISIBLEIFS - User Defined Function to exclude hidden cells.* You can use the DAVERAGE function to return the arithmetic mean from a column that satisfies multiple conditions.* This is similar to the COUNTIFS, MAXIFS, MINIFS and SUMIFS functions.* For the Microsoft documentation refer to support.microsoft.com* For the Google documentation refer to support.google.com

 A B C 1 =AVERAGEIFS(C1:C5, B1:B5, "Jan") = 25 Jan 30 2 =AVERAGEIFS(C1:C5, B1:B5, "=Jan") = 25 Feb 40 3 =AVERAGEIFS(C1:C5, B1:B5, "=Feb") = 50 Jan 20 4 =AVERAGEIFS(C1:C5, B1:B5, "Jan", C1:C5, "<>20") = 30 Feb 60 5 =AVERAGEIFS(C1:C5, B1:B5, "Jan", C1:C5, "=20") = 20 Mar 80 6 =AVERAGEIFS(C1:C5, B1:B5, "Jan", C1:C5, ">" & C3) = 30

 1 - What is the average of all the numbers in the range "C1:C5" that have "Jan" in the range "B1:B5",2 - What is the average of all the numbers in the range "C1:C5" that have "Jan" in the range "B1:B5". Notice that prefixing the condition with an equal sign means the same thing.3 - What is the average of all the numbers in the range "C1:C5" that have "Feb" in the range "B1:B5".4 - What is the average of all the numbers in the range "C1:C5" that have "Jan" in the range "B1:B5" and have a value not equal to 20 in the range "C1:C5".5 - What is the average of all the numbers in the range "C1:C5" that have "Jan" in the range "B1:B5" and have a value equal to 20 in the range "C1:C5".6 - What is the average of all the numbers in the range "C1:C5" that have "Jan" in the range "B1:B5" and have a value greater than the value in cell "C3" in the range "C1:C5".6 - include wildcards and date filtering.