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.
Leave a Reply