Returns the average of values in a range if they meet multiple criteria specified in other ranges.

Parameter List


Range of values to be averaged.


Range of conditional values to check against criterion1. Must be of the same size (rows and columns) as average_range.


The condition to apply to criteria_range1.

[optional] criteria_range2

Additional range of conditional values to check against criterion2.

More details
  • If ranges contains text to check against, criterion must be a string.

  • Criterion can contain wildcards including ? to match any single character or * to match zero or more contiguous characters. To match an actual question mark or asterisk, prefix the character with the tilde (~) character (i.e. ? and *).

  • A string criterion must be enclosed in quotation marks. Each cell in range is then checked against criterion for equality (or match, if wildcards are used).

  • If range contains numbers to check against, criterion may be either a string or a number. If a number is provided, each cell in range is checked for equality with criterion. Otherwise, criterion may be a string containing a number (which also checks for equality), or a number prefixed with any of the following operators: = (checks for equality), > (checks that the range cell value is greater than the criterion value), or < (checks that the range cell value is less than the criterion value).


AVERAGEIFS(A1:A5, B1:B5, ">20") returns 1