AVERAGEIF function takes the average of the values in a range of cells that match a criteria. In this guide, we’re going to show you how to use the AVERAGEIF function and also go over some tips and error handling methods.
Supported versions
- All Excel versions
Syntax
Arguments
range |
The range of cells that you want to apply the criteria against |
criteria |
The criteria that is applied to range to define which cells to calculate. |
average_range |
Optional. The range of cells whose average will be calculated. |
Examples
We’re using named ranges in our examples to make the formulas easier to read. You don’t need to do this to run your formulas.
Example 1
Example 2
Note: AVERAGEIF function is not case-sensitive – “FIRE” and “fire” criteria will give the same results.
Tips
- Use same number of rows and columns for sum and criteria range arguments.
- Bad Example: =AVERAGEIF(F2:H10,”>2014″,G2:G15)
- Good Example: =AVERAGEIF(F2:F11,”>2014″,G2:G11)
- Comparison operators:
Operator
Description
Criteria Sample
Criteria Meaning
=
Equal to
“=10000”
Equal to 10000
<>
Not equal to
“<>10000”
Not equal to 10000
>
Greater than
“>10000”
Greater than 10000
<
Less than
“>10000”
Less than 10000
>=
Greater than or equal to
“>=10000”
Greater than or equal to 10000
<=
Less than or equal to
“<=10000”
Less than or equal to 10000
?
Takes the place of a single character
“Admin?”
6-character word starts by “Admin”
*
Can take the place of any number of characters.
“Admin*”
Any number of character word starts with “Admin”
~
Use tilde in front of a question mark or an asterisk to actually find them
“Admin~*”
Equal to “Admin*”
Note: Wildcards cannot be used for numeric values. Searching for a wild card character in a range of numeric values will return no matches.
Issues
#VALUE!
The AVERAGEIF function returns incorrect results when you use it to match strings longer than 255 characters, or the string #VALUE!.
TRUE and FALSE
TRUE and FALSE values in average_range are evaluated as numbers. While TRUE is evaluated as 1, FALSE is evaluated as 0. As a result, this property can cause unexpected results when used in other calculations.