Excel AVERAGEIF Function
AVERAGEIF averages values that match one condition.
Use it for one-condition averages, such as average order value for one region, average score above a threshold, or average time for one task type.
AVERAGEIF syntax & arguments
Syntax
=AVERAGEIF(range, criteria, [average_range])
-
1
range
RequiredThe cells to test against the criterion.
-
2
criteria
RequiredThe condition that decides which rows or cells are included. It can be a number, text, cell reference, expression such as
">80", or wildcard pattern. -
3
average_range
OptionalThe cells to average when the matching cell in
rangemeets the criterion. If omitted, Excel averages the matching cells inrange.
Example
=AVERAGEIF(B2:B50, "East", D2:D50)
Average values in D2:D50 where the matching cell in B2:B50 is East.
AVERAGEIF caveats
-
Range alignment matters
rangeandaverage_rangeshould cover the same rows or columns so the tested cells line up with the values being averaged. -
Operators usually need quotes
Criteria such as
">80"or"<>Cancelled"must be written as text. -
It only handles one condition
Use AVERAGEIFS instead when the average depends on multiple criteria, such as region and month.
-
Only numeric average cells count
Blank or text cells inside
average_rangeare skipped, so the denominator is the count of matching numeric values.
Need a conditional total? Use SUMIF when matching cells should drive values that are added instead of averaged.
Intro AVERAGEIF practice problems
No intro AVERAGEIF problems are currently available.
Advanced AVERAGEIF practice problems
Use AVERAGEIF alongside other Excel functions in realistic, less-prescriptive challenges.
