HOW TO USE AVERAGEIFS FUNCTION IN EXCEL


AVERAGEIFS FUNCTION


HOW TO USE AVERAGEIFS FUNCTION IN EXCEL

This article explains How to use the Excel AVERAGEIFS function with syntax and examples

DESCRIPTION

The Microsoft Excel AVERAGEIFS function returns the average (arithmetic means) of all numbers in a range of cells, based on multiple criteria

SYNTAX

The syntax of the AVERAGEIFS function in Microsoft Excel is:

=AVERAGEIFS( average_range, criteria1_range, criteria1, [criteria2_range, criteria2, criteria3_range, criteria3, ...] )

average_range

The range of cells that you wish to average.

criteria1_range, criteria2_range. . . . . criteria127_range

The range to apply the associated criteria against. There can be up to 127 ranges and associated criteria

criteria1, criteria2. . . . .criteria127

The criteria to apply against the associated range. So criteria1 is applied against criteria1_range, criteria2 is applied against criteria2_range, and so on

TYPE OF FUNCTION

Worksheet function




REMARKS


  • If average_range is a blank or text value, AVERAGEIFS returns the #DIV0! error value.
  • If a cell in a criteria range is empty, AVERAGEIFS treats it as a 0 value.
  • Cells in range that contain TRUE evaluate as 1; cells in range that contain FALSE evaluate as 0 (zero).
  • Each cell in average_range is used in the average calculation only if all of the corresponding criteria specified are true for that cell.
  • Unlike the range and criteria arguments in the AVERAGEIF function, in AVERAGEIFS each criteria_range must be the same size and shape as sum_range.
  • If cells in average_range cannot be translated into numbers, AVERAGEIFS returns the #DIV0! error value.
  • If there are no cells that meet all the criteria, AVERAGEIFS returns the #DIV/0! error value.
  • You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

APPLIES TO

Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Online, Excel for iPad, Excel for iPhone, Excel for Android tablets, Excel Starter, Excel for Android phones


EmoticonEmoticon