Description
Use this function to calculate the average of numbers in a range that meet multiple criteria.
AVERAGEIFS is useful for analyzing data based on multiple conditions, such as averaging sales data for a particular product in a specific region.
Syntax
AVERAGEIFS(average_range, criteria_range1, criterion_1, […,criteria_range_127, criterion_127])
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
average_range |
Yes | The range of cells to average. | A cell reference, a cell range, or a formula which results in either of these. |
criteria_range_1 |
Yes | The first range to evaluate with the corresponding criteria. |
A cell reference, a cell range, or a formula which results in either of these. |
criterion_1 |
Yes | The condition that must be met for cells in criteria_range_1. |
A number, a text string, a cell reference, a cell range, or a formula which results in any of these. |
criteria_range_ncriterion_n
|
No | Additional ranges and their corresponding criteria (up to 127 additional pairs). | These require the same types of input as criteria_range_1 and criterion_1. |
Example
Sample data
| A | B | C | D |
|---|---|---|---|
| Region | Product | Sales | Month |
| North | Apples | 120 | January |
| South | Oranges | 150 | January |
| North | Bananas | 90 | February |
| South | Apples | 200 | February |
| North | Oranges | 85 | March |
Sample formulas
| Formula | Description | Result |
|---|---|---|
=AVERAGEIFS(C2:C7, A2:A7, "North", B2:B7, "Apples") |
Calculates the average sales for Apples in the North region. | 120 |
=AVERAGEIFS(C2:C7, B2:B7, "Oranges", D2:D7, "January") |
Calculates the average sales for Oranges in January. | 150 |
=AVERAGEIFS(C2:C7, A2:A7, "South", D2:D7, "February") |
Calculates the average sales for the South region in February. | 200 |
=AVERAGEIFS(C2:C7, A2:A7, "North", D2:D7, "<>January") |
Calculates the average sales for the North region in months other than January. | 87.5 |
=AVERAGEIFS(C2:C7, B2:B7, "<>Bananas", C2:C7, ">=100") |
Calculates the average sales for products other than Bananas with sales of 100 or more. | 156.67 |
Notes
- Only cells that meet all conditions are included in the final result.
- AVERAGEIFS will return a #VALUE! error if every range and criteria pair is not the same size as the first range and criteria pair.
- AVERAGEIFS returns the #DIV/0! error if no cells meet the criteria, or if any cell in
average_rangecannot be translated into numbers. - AVERAGEIFS treats any empty cells in a criteria range as having a value of 0 (zero).
- Cells that contain TRUE evaluate as 1; cells that contain FALSE evaluate as 0 (zero).
- AVERAGEIFS supports logical operators (>,<,<>,=). In general, text values are enclosed in double quotes (""), and numbers are not. However, when a logical operator is included with a number, both the number and operator must be enclosed in quotes.
- AVERAGEIFS supports wildcards (*,?) for partial matching. 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, enter a tilde (~) before the character.
-
AVERAGEIFS measures central tendency, which is the location of the center of a group of numbers in a statistical distribution. The three most common measures of central tendency are:
-
Average — The arithmetic mean, calculated by adding a group of numbers and then dividing by the count of those numbers.
For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5.
See AVERAGE for more information on this calculation. -
Median — The middle number of a group of numbers; that is, half the numbers have values that are greater than the median value, and half the numbers have values that are less than the median.
For example, the median of 2, 3, 3, 5, 7, and 10 is 4.
See MEDIAN for more information on this calculation. -
Mode — The most frequently occurring number in a group of numbers.
For example, the mode of 2, 3, 3, 5, 7, and 10 is 3. Workiva doesn't offer a MODE calculation.
For a symmetrical distribution of a group of numbers, these three measures of central tendency will result in the same value. If the number distribution is not symmetrical (for example, a preponderance of low values, with only a few high values), they will be different.
-
Average — The arithmetic mean, calculated by adding a group of numbers and then dividing by the count of those numbers.
Tips
- Use AVERAGEIFS for complex data analysis involving multiple conditions, such as time periods and categories.
- Use AVERAGEIFS with other functions such as SUMIFS and COUNTIFS for comprehensive data analysis.
- When working with dates, use DATE in your criteria for more precise filtering.
Related functions
AVERAGE
AVERAGEA
AVERAGEIF
AVERAGEIFS
LARGE
MAX
MAXA
MAXIFS
MEDIAN
MIN
MINA
MINIFS
PERCENTILE
PERCENTILE.EXC
PERCENTILE.INC
QUARTILE
QUARTILE.EXC
QUARTILE.INC
RANK
RANK.AVG
RANK.EQ
SMALL
STDEV
STDEV.P
STDEV.S
STDEVA
STDEVPA