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.