Excel AVERAGEIFS Function

This tutorial explains how to use the Excel AVERAGEIFS function.

Description

The Excel AVERAGEIFS function calculate the average of cells in a range that meet multiple criteria.

There are other AVERAGES functions that you can use to calculate the average of a group of numbers (AVERAGE, AVERAGEIF, and AVERAGEA).

  • Use the Excel AVERAGE function if you want to calculate the average of a group of numbers.
  • Use the Excel AVERAGEIF function if you want to calculate the average of only the values that meet single criteria.
  • Use the Excel AVERAGEA function if you want to include logical values and text representations of numbers in a reference as part of the calculation.

Syntax
=AVERAGEIFS (average_range, range1,criteria1, [range2, criteria2],…)
Arguments

  • average_range: A range of cells to average.
  • range1: The first range to evaluate the associated criteria (criteria1)
  • criteria1:  The first criteria that defines which cells will be averaged.
  • range2: [Optional]. The second range to evaluate the associated criteria (criteria2)
  • criteria2: [Optional]. The second criteria that defines which cells will be averaged.

You can specify up to 127 range/criteria pairs.

range1, range2 can numbers, ranges, names, arrays, or cell references.

criteria1, criteria2 can be a number, cell reference, expression, or text.

If average_range is a blank or text value, AVERAGEIFS returns #DIV0!.

AVERAGEIFS returns the error value (#DIV/0!) if there is no cells in the range meet the criteria.

AVERAGEIFS allows the wildcard characters (*) and (?) in criteria.

Each criteria_range must be the same size and shape as average_range.

Example

Here is our sample data.

A B C D E F G
1 Date Code Item Name Quantity Price ($) Amount ($) Employee
2 7/10/2017 Item001 Sony Desktop PC 2 225 450 Roland
3 7/10/2017 Item002 Dell Desktop PC 2 750 1500 Roland
4 7/10/2017 Item001  Sony Desktop PC 1 235 235 James
5 7/10/2017 Item003 HP Desktop PC 3 785 2355  Roland
6 7/11/2017 Item002 Dell Desktop PC 3 1050 3150  Roland
7 7/12/2017 Item001 Sony Desktop PC 1 275 275 Roland
8 7/12/2017 Item003 HP Desktop PC 4 290  1160 James

Example 1:

In this We want to get the average of the sales amount by James and we can write the following formula.

=AVERAGEIFS(F2:F8,G2:G8, James”)

  • F2:F8 is average_range.
  • G2:G8 is range1.
  • “James” is crieteria1.

In Example 1, AVERAGEIFS calculates the average of the sales amount by James and returns 697.5.

Example 2:
In Example 2, if we want to calculate the average of sales amount by an employee on the specific date, we can write the following formula. As you can see, there are two criteria. DATE(2017,7,10) is the first criteria and “Roland” is the second criteria.

=AVERAGEIFS(F2:F8,A2:A8,DATE(2017,7,10),G2:G8,“Roland”)

  • F2:F8 is average_range.
  • A2:A8 is range1.
  • DATE(2017,7,10) is criteria1.
  • G2:G8 is range2.
  • “Roland” is crieteria2.

In this example, the AVERAGEIFS function calculates the average of the sales amount by Roland on July 10th 2017 and returns 1435.

Example 3:
Let’s see another example below. We’ll use the wildcard character (*) in criteria in this example.
If we want to calculate the average of the sales amount by an employee that name starts with R and ends with ND, we can use the following formula.

=AVERAGEIFS(F2:F8,G2:G8, “R*ND”)

The AVERAGEIFS calculates the average of the sales amount by an employee that name starts with R and ends with ND. As you can see, we use * between R and ND to match any sequence of characters between R and ND. The output of the AVERAGEIFS formula is 1546.

Note*: The wildcard (*) matches any sequence of characters.

Example 4:
Lest’s another example that uses (?) in criteria.

=AVERAGEIFS(F2:F8,G2:G8, “Jam??”, B2:B8, “Item001”)

In Example 4, the AVERAGEIFS calculates the average of the sales amount of item0001(Sony Desktop PC) by all employees that name starts with Jam and ends with any two characters. The result is 235.

Note*: The wildcard (?) matches any single character.

In this tutorial, you have learned how to use the Excel AVERAGEIFS function. We use the Excel AVERAGEIFS function to calculate the average of cells in a range that meet multiple criteria.

Here is a full list of Microsoft Excel functions.