Function: AVERAGEIF

by | Dec 21, 2018 | Excel Formulas

AVERAGEIF-1

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

AVERAGEIF(range, criteria, [average_range])

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

=AVERAGEIF(Sp.Atk,”<100″)
formula calculates the average of the Sp.Atk values where Sp.Atk is less than 100. Since both criteria and average ranges are the same, average_range argument can be omitted.

AVERAGEIF-1

Example 2

=AVERAGEIF(Type,”FIRE*”,HP)
formula calculates the average of HP values, where Type starts with the string “FIRE“. If you use “FIRE” without asterisks, the AVERAGEIF function will skip the HP values for “FIRE, GROUND” and “FIRE, FLYING“.

Note: AVERAGEIF function is not case-sensitive – “FIRE” and “fire” criteria will give the same results.

AVERAGEIF-2

Download Workbook

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.