Each value is compared to condition. If the value meets the conditional test, the corresponding value in avg-values is included in the average.
avg-values and test-values (if specified) must be the same size.
If avg-values is omitted, test-values is used for avg-values.
If avg-values is omitted or is the same as test-values, test-values can contain only number values or boolean values.
If more than one condition or set of test-values is required, consider using the AVERAGEIFS function.
Examples
Given the following table:
A
B
C
D
1
Age
Sex
Status
Salary
2
35
M
71,000
3
27
F
81,000
4
42
86,000
5
51
S
66,000
6
28
52,000
7
49
62,000
8
63
89,000
9
22
34,000
10
29
42,000
11
56,000
12
33
13
61
91,000
=AVERAGEIF(A2:A13, "<40", D2:D13) returns approximately 56,857, the average salary of people under the age of 40.
=AVERAGEIF(B2:B13, "=F", D2:D13) returns 66,000, the average salary of females (indicated by an "F" in column B).
=AVERAGEIF(C2:C13, "S", D2:D13) returns 56,800, the average salary of people who are single (indicated by an "S" in column C).
=AVERAGEIF(A2:A13, ">=40", D2:D13) returns 78,800, the average salary of people who are 40 or older. You can also use the ≥ operator.
=AVERAGEIF(C2:C13, "<>M", D2:D13) returns 56,800, the average salary of people who are not married. You can also use the ≠ operator
Example using REGEX
45
[email protected]
41
Aaron
64
Sarah
=AVERAGEIF(B1:B5, REGEX("([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})"), A1:A5) returns 46, the average of all the cells in A1:A5 where the corresponding cell in B1:B5 contains an email address.