AVERAGEIF Function | Excel

Excel provides conditional functions which allow the user to average all the cells in a range that meets single or multiple criteria. The following paragraphes will explain two types of these functions called "Averageif" and "Averageifs". They could check single and multiple criteria, respectively. This tutorial assumes you have a working knowledge of Average & If functions.

Averageif

The Averageif function or statement is used to average all the cells in a range that meets single criteria. Typically, this function consists of three arguments (parts) enclosed in one set of parentheses and each is separated by a comma, as shown below:

=AVERAGEIF(Range, Criteria, [Average Range])

Where,

• ● RANGE is one or more cells to average, including numbers or names, arrays, or references that contain numbers.
• ● CRITERIA is the criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged. For example, criteria can be expressed as 32, "32", ">32", "balls", or B4.
• ● AVERAGE_range is the actual set of cells to average. If omitted, RANGE is used.

The formula can be inserted either by typing it or by selecting it from the Function Library on the formula’s ribbon, under heading of more functions in statistical. The Function Library allow to view the above syntax (details) of AVERAGEIF function, using Mouse as follow:

• i. Click on the FORMULAS Ribbon.
• ii. Click the drop down arrow below the MORE FUNCTIONS button, go to STATISTICAL and select AVERAGEIF from the displayed functions.
• 
  
  
  
• iii. The next dialog box will appear and the three arguments can be seen within the box.
• 
  
  
  
• iii. Click in RANGE text box.
• iv. Select the range of cells containing the values you wish to check against the criteria.
• v. Click in the CRITERIA box, either, type criteria directly in the box or select a cell that contains the value to check against your RANGE.
• vi. Click in the AVERAGE_RANGE text box and select the range you wish to average and Click OK.

In next example, returns the average of C2:C5 where the corresponding value in column B is greater than 250,000:



The next formula is used with one criteria:

=AVERAGEIF(B2:B5, “>250000”, C2:C5)

Averageifs

Averageifs function or statement is used to average all the cells in a range that meets multiple criteria. Typically, this function consists of more than or equal to three arguments (parts) enclosed in one set of parentheses and each is separated by a comma, as shown below:

=AVERAGEIFS(Average_range,criteria_range1,criteria1,criteria_range2,criteria2…)

Where,

• ● AVERAGE_RANGE is one or more cells to average, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
• ● CRITERIA_RANGE1, CRITERIA_RANGE2, … are 1 to 127 ranges in which to evaluate the associated criteria.
• ● CRITERIA1, CRITERIA2, … are 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be averaged. For example, criteria can be expressed as 32, "32", ">32", "balls", or D6.

The formula can be inserted either by typing it or by selecting it from the Function Library on the formula’s ribbon, under heading of more functions in statistical. The Function Library allow to view the above syntax (details) of AVERAGEIFS function, using Mouse as follow:

• i. Click on the FORMULAS Ribbon.
• ii. Click the drop down arrow below the MORE FUNCTIONS button, go to STATISTICAL and select AVERAGEIFS from the displayed functions.
• iii. The next dialog box will appear and more than three arguments can be seen within the box.
• 
  
  
  
• iv. Click in AVERAGE_RANGE text box.
• v. Select the range you wish to average.
• vi. Click in the CRITERIA_RANGE1 box select a range of cells that contains the values you wish to check against the criteria1
• vii. Click in the CRITERIA1 text box and type in the criteria to check against CRITERIA_RANGE1.
• viii. Repeat steps v and vi to enter multiple criteria, range2, range3 etc, use the scroll bar on the right to scroll down and locate more range and criteria text boxes. Click OK when all ranges and criterias have been entered.

Some important points about AVERAGEIFS:

• ☺ If AVERAGE_RANGE is a blank or text value, or if cells in AVERAGE_RANGE cannot be translated into numbers, or if there are no cells that meet all the criteria, AVERAGEIFS returns the #DIV/0! 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, while cells in range that contain FALSE evaluate as 0.
• ☺ 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 average_range.
• ☺ Wildcard characters, question mark (?) and asterisk (*), could be used in criteria. Find more explanation on "The Use of Wildcard Characters" in Sumif Tutorial.

In next example, the average grades of students are calculated matching multipile criteria for several quizes.