AVERAGEIFS()

Syntax. AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2,...)

Note

The AVERAGEIFS() function was new in Excel 2007.

Definition. This function returns the average of all cells that meet several criteria.

Arguments

• average_range (required). One or more cells to average, including numbers, names, arrays, or references that contain numbers

• criteria_range1 (required). The range in which to evaluate the associated criteria

• criteria1 (required). The criteria in the form of a number, an expression, a cell reference, or text that defines which cells will be averaged

• criteria_range2 (optional). At least one and up to 127 ranges containing the criteria to be evaluated

• criteria2 (optional). At least one and up to 127 criteria in the form of a number, an expression, a cell reference, or text that defines which cells will be averaged

Note

If average_range is empty or is a text value, AVERAGEIFS() returns the #DIV0! error.

If a cell in a criteria range is empty, AVERAGEIFS() treats it as a 0 value. Cells in a range that contain TRUE evaluate as 1. Cells in a range that contain FALSE evaluate as 0.

Each cell in average_range is used in the calculation only if all of the criteria specified are true for that cell.

Unlike the range and criteria arguments of the AVERAGEIF() function, the criteria_range for AVERAGEIFS() has to be the same size and shape as average_range.

If a cell in average_range cannot be converted into a number, AVERAGEIFS() returns the #DIV0! error.

If no cells in the range meet the criteria, AVERAGEIFS() returns the #DIV0! error.

You can use the wildcard characters ? (question mark) and * (asterisk) as criteria. The question mark stands for a character and the asterisk for a string. If you want to search for an actual question mark or asterisk, enter a tilde (~) before the character.

Background. You will find more information about the average in the description of AVERAGE(). Like the AVERAGEIF() function, the AVERAGEIFS() function measures the central tendency.

For the AVERAGEIFS() function, you can specify criteria that are determinative for the calculation of the mean value. You will find more information in the description of AVERAGEIF().

Example. Let’s stay with the example of the mailing. You wanted to know the average click rate on your website after a mailing. Now you want to specify another criterion to include only click rates higher than 10,000 in the calculation of the mean value. This way you can ensure that the result is not impacted by outliers.

As you can see in Figure 12-8, the formula uses the following arguments:

• average_range: This is cell range D2:D30 because this column contains the values from which the mean value is calculated.

• criteria_range1: This is cell range C2:C30 because these cells contain criteria on which the evaluation is based.

• criteria1: Software A is specified, because you only want to know the mean value of the click rate after this mailing was sent.

• criteria_range2: This is cell range D2:D30 because these cells contain criteria on which the evaluation is based.

• criteria2: “>10000” is specified because you only want to know the mean value of the click rates higher than 10,000.

You can make the following statement from these results: For the past 30 months, the average click rate after the Software A mailing was 15,221.50.

Figure 12-8. Calculating the average with AVERAGEIFS().

If you calculate the average click rate for the Software B and Software C mailings, the result shows that the average click rate after the Software A mailing is the highest, as shown in Figure 12-9.

Figure 12-9. Calculating additional mean values by using different criteria.

See Also

AVERAGE(), AVERAGEIF(), COUNTIF(), MEDIAN(), MODE.SNGL()

Sample Files

Use the AVERAGEIFS worksheet in the Average.xlsx sample file. The sample files are found in the Chapter12 folder. For more information about the sample files, see the section titled Using the Sample Files.