Returns the average of values in a range if they meet a criterion specified in another range.

Parameter List


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


Pattern or test to apply to the range.

[optional] average_range

Range of values to be averaged.

More details
  • If range 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).


AVERAGEIF(A1:A5, ">20") returns 25