AVERAGEIFS

Purpose

• Calculates the average of a range of numbers, if certain user-defined criteria are met
  • If no criteria are met, an average cannot be calculated and thus an error will occur

Example

• As the following screenshot shows, AVERAGEIFS works almost exactly like SUMIFS, but you are averaging numbers instead of adding them:

• As you can see in this example, we average certain values in column D based on the following two criteria:
  • The corresponding Amount is less than or equal to whatever value is in C15 (hence the "<="&C15 argument)
  • The corresponding Account is whatever is in cell C16, which is currently 222

Syntax

• =AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, etc.)

Arguments

• average_range
  • This is the target range of cells that you want to average
  • The values in this range should be numerical, since we are computing a average on this range
    • However, if cells containing text are in this range, they will be treated as zeroes
  • AVERAGEIFS will only average cells in the average_range in which all criteria (defined in the remaining arguments) are met
• criteria_range1
  • This range of cells should contain data that either match or do not match the criteria1 you define in the next argument
  • This range must contain the same number of cells as the average_range argument
  • If the average_range is a vertical (horizontal) range of cells, criteria_range1 must also be a vertical (horizontal) array of cells
  • This range can contain numerical values or text
  • This range can be the same as the average_range, if necessary
• criteria1
  • This is the actual criteria that must be met (in the criteria_range1) in order to be included in the average
  • Carefully review the following screenshot, which provides a few examples of how you can write criteria and what they mean:

• criteria_range2 [optional]
  • This is the next range which either matches or does not match criteria2 you define in the next argument
  • This range has the same properties as criteria_range1
  • In the example above, since we want only to average a certain account (in the blue cell), criteria_range2 is the Accounts (B4:B13)
• criteria2 [optional]
  • This is the actual criterion that must be met (in the criteria_range2) in order to be included in the average
  • This range has the same properties as criteria1
  • In the example above, since we only want to average those amounts whose Accounts equal the value in cell C16, criteria2 is simply equal to cell C16
• The pattern continues up to 127 times
  • If you include any more criteria ranges (e.g. criteria_range3, you must also include a corresponding criteria (e.g. criteria3)
  • All future criteria_range arguments have the same properties as the criteria_range1 argument
  • All future criteria arguments have the same properties as the criteria1 argument

Tips

• Be careful with blank values! The AVERAGEIFS function ignores blank values completely, but will not ignore zero values (as shown in the illustration above)
• Again, this function works exactly like SUMIFS, except the data are averaged instead of added
• The AVERAGEIF (not ending in "S") function can be used if you have just one criteria that needs to be met, but why use AVERAGEIF if you can always use AVERAGEIFS?
  • Note that the arguments in AVERAGEIF are arranged differently than those in AVERAGEIFS
  • As such, using AVERAGEIFS will allow you to easily add more criteria without having to change the arrangement of your arguments

External Links

• Microsoft: AVERAGEIFS Function