Using the Excel AVERAGEIFS Function


The Excel AVERAGEIFS function returns the average, or arithmetic mean, of cells that meet a user specified condition.


=AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Syntax Breakdown

Average Range
Required. One or more cells that will be averaged. May include numbers, names, arrays, or references that contain numbers.

Criteria Range 1
Required. First range to evaluate associated criteria.

Criteria 1
Required. Criteria in the form of a number, expression, cell reference, or text that specify which cells should be averaged. For example, criteria can be provided as 15, "15", "<15", "students", or A1. When using non-numeric criteria, ensure the criteria is enclosed in double quotation marks (" "). Empty cells are treated as a zero (0) value.

Criteria Range 2
Optional. Criteria range 2 and subsequent criteria ranges are optional. The function can accept up to 127 ranges.

Criteria 2
Optional. Criteria 2 and subsequent criteria are optional. The function can accept up to 127 criteria.

Usage Notes

AVERAGEIFS returns the average of a group of supplied values that meet one or more supplied criteria. The criteria supports logical operators (<, >, =, <>) and wildcard characters (* and ?).

When using wildcard characters, the question mark (?) is used to match any one character and the asterisk (*) is used to match a sequence of characters. If you must find an actual question mark or asterisk, use a tilde (~) in front of the question mark or asterisk, for example ~? or ~*.

Unlike the AVERAGEIF function, AVERAGEIFS accepts more than one set of criteria and more than one range. The criteria must be supplied in pairs (range/criteria). The range that will be averaged is the first range.

Additional ranges must have the same number of rows and columns as the average_range argument.

AVERAGEIFS returns the #DIV0! error if none of the criteria are met or if cells in the average_range argument cannot be translated into numbers.

AVERAGE, MEDIAN, and MODE are used to measure the central tendency in a statistical distribution. The functions perform as follows.

AVERAGE is used to find the arithmetic mean. This is done by adding up all of the numbers in a group and dividing by the total count of numbers. For example, the average of 1, 2, 3, and 4 is found by adding the numbers together (1+2+3+4 = 10) and dividing by 4 (10/4) to produce 2.5.

The MEDIAN is the middle number in a group of numbers. In the group, 50% of the numbers are above the median and 50% are below the median. For example, in the number set 1, 5, 7, 8, 15 the MEDIAN is 7.

The MODE is the number that occurs most frequently in a group of numbers. In the number set 1, 3, 3, 3, 8 the MODE is 3.