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 

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. 
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top