AVERAGEIFS Function in Excel finds and returns the arithmetic mean or average of array that meets one or more conditions. The AVERAGEIFS function in Excel supports logical operators (>,<,<>,=) and wildcards (*,?) for pattern matching.
Wild cards in AVERAGEIFS Function
- ? – matches any single character
- * – matches any sequence of characters
Example:
The condition “A?e” will match all cells containing a text string beginning with “A” and ending in “e” and only one character in between i.e. A_e.
The condition “A*e” will match all cells containing a text string beginning with “A” and ending in “e” with multiple characters(n) in between i.e. A_______e.
Syntax of AVERAGEIFS Function in Excel:
AVERAGEIFS( average_range, criteria_range1, criteria1, [criteria_range2, criteria2], … )
- average_range – array of numeric values for which the average is to be calculated.
- criteria_range1 – Arrays of values to be tested against the respective i.e. criteria1
- criteria1– The criteria or condition on which average has to be calculated.
Example of AVERAGEIFS Function in Excel
Formula
- First two examples states the use of logical operation in AVERAGEIFS function.
- Last two examples states the use of wildcards in AVERAGEIFS function.
So the result will be like