AVERAGEIFS
The AVERAGEIFS function returns the average (arithmetic mean) of the cells in a given set where one or more sets meet one or more related conditions.
AVERAGEIFS(avg-values, test-values, condition, test-values…, condition…)
avg-values: A collection containing the values to be considered for the average value. avg-values is a reference to a single collection of cells, which can contain any value.
test-values: A collection containing values to be tested. test-values can contain any value.
condition: An expression that compares or tests values and results in the boolean value TRUE or FALSE. condition can include comparison operators, constants, the ampersand concatenation operator, references, and wildcards. You can use wildcards to match any single character or multiple characters in the expression. You can use a ? (question mark) to represent one character, an * (asterisk) to represent multiple characters, and a ~ (tilde) to specify that the following character should be matched rather than used as a wildcard. condition can also contain a REGEX function instead of wildcards.
test-values…: Optionally include one or more additional collections containing values to be tested. Each test-values collection must be followed immediately by a condition expression. This pattern of test-values, condition can be repeated as many times as needed.
condition…: If an optional collection of test-values is included, condition… is an additional expression that results in a boolean value TRUE or FALSE. There must be one condition expression following each test-values collection; therefore, this function always has an odd number of arguments.
Notes
For each of the test-values and condition pairs, the corresponding (same position within the array) value is compared to the conditional test. If all of the conditional tests are met, the corresponding value in avg-values is included in the average.
avg-values and all test-values collections must be the same size.
Examples |
|---|
Given the following table: |
A | B | C | D | |
|---|---|---|---|---|
1 | Age | Sex | Status | Salary |
2 | 35 | M | M | 71,000 |
3 | 27 | F | M | 81,000 |
4 | 42 | M | M | 86,000 |
5 | 51 | M | S | 66,000 |
6 | 28 | M | S | 52,000 |
7 | 49 | F | S | 62,000 |
8 | 63 | F | M | 89,000 |
9 | 22 | M | M | 34,000 |
10 | 29 | F | S | 42,000 |
11 | 35 | F | M | 56,000 |
12 | 33 | M | S | 62,000 |
13 | 61 | M | M | 91,000 |
=AVERAGEIFS(D2:D13, A2:A13, "<40", B2:B13, "=M") returns 54,750, the average salary of males (indicated by an "M" in column B) under the age of 40. =AVERAGEIFS(D2:D13, A2:A13, "<40", B2:B13, "=M", C2:C13, "=S") returns 57,000, the average salary of males who are single (indicated by an "S" in column C) under the age of 40. =AVERAGEIFS(D2:D13, A2:A13, "<40", B2:B13, "=M", C2:C13, "=M") returns 52,500, the average salary of males who are married (indicated by an "M" in column C) under the age of 40. =AVERAGEIFS(D2:D13, A2:A13, ">=40", B2:B13, "=M", C2:C13, "=M") returns 88,500, the average salary of males who are married (indicated by an "M" in column C) and are at least 40 years old. You can also use the ≥ operator. =AVERAGEIFS(D2:D13, A2:A13, "<40", B2:B13, "=F") returns approximately 59,667, the average salary of females (indicated by an "F" in column B) who are under the age of 40. =AVERAGEIFS(D2:D13, A2:A13, "<>35", C2:C13, "=S") returns 56,800, the average salary of people who are not 35 and are single. You can also use the ≠ operator. |
Example using REGEX |
|---|
Given the following table: |
A | B | |
|---|---|---|
1 | 45 | |
2 | 41 | Aaron |
3 | 29 | |
4 | 64 | |
5 | 12 | Sarah |
=AVERAGEIFS(A1:A5, B1:B5, REGEX("([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})"), A1:A5, ">10") returns 46, the average of all the cells in A1:A5 that are larger than 10 and where the corresponding cell in B1:B5 contains an email address. |