AVERAGEIFS Function in Excel – Calculate the Arithmetic Mean or Average based on conditions

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

AVERAGEIFS Function in Excel 1

  • 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

Result

AVERAGEIFS Function in Excel 2

previous AVERAGEIFS Function in Excel                                                                                                                next AVERAGEIFS Function in Excel