Calculate the average of numbers in a range that meet specific criteria.

AVERAGEIF(range, criteria, [average_range])

---

AVERAGEIF (without the 's'!) allows you to calculate the average of values associated with one set of specific criteria in a range or table.

The AVERAGEIF syntax requires the range to be searched, the criteria to look for and the range of values to be averaged.

AVERAGEIF can be used with dates, numbers, and text and supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

---

AVERAGEIF and text.

In the examples below, the data range shows a list of countries, the region they belong to and their respective Gross Domestic Product (GDP) per capita in current US$ (2018). AVERAGEIF is used to determine average GDP per capita for countries in specific regions or with a certain amount of letters in their country name.

Note: AVERAGEIF works slightly differently to the AVERAGEIFS (with the 's'!) function which allows you to average values associated with multiple criteria. For example, with an AVERAGEIFS function, you could determine average GDP per capita for all countries in a specific region where country population was below a certain threshold. This would not be possible with the AVERAGEIF function which only permits one criteria.

---

AVERAGEIF and numbers.

In the examples below, AVERAGEIF is used to determine the average GDP per capita for countries based on population or GDP per capita delimiters. Note that the logical operators (<>) are included in quotations with stated values (e.g. (">=2000") but are concatenated with cell references (e.g. "<=" & C14).

---

AVERAGEIF and dates.

In the examples below, AVERAGEIF is used to determine average sales for given dates or date ranges. Dates can be expressed as stated values ("1-Mar-22") or as cell references or using the DATE function (=DATE(2022,3,1)).

---

Syntax

The AVERAGEIF function consists of 3 arguments: =AVERAGEIF(range,criteria,[average range])

1. range. Required. The range of cells to be evaluated by the criteria.
2. criteria. Required. The criteria used to evaluate the range.
3. [average_range]. Optional. The range of cells containing the values to average.
   • when omitted, range is used.

Try it now!

• Enter your formulas into the grey cells.

---

Scope

AVERAGEIF can be applied to ranges, named ranges and tables. It can also be used with logical conditions, wildcards and cell references.

Range:	=AVERAGEIF(C3:C13,"South Asia",D3:D13)
Named Range:	=AVERAGEIF(Region,"South Asia",Population)
Table:	=AVERAGEIF(Table1[Region],"South Asia",Table1[Population])
Logical Condition:	=AVERAGEIF(C3:C13,"<>South Asia",D3:D13) // not equal to "South Asia"
Wildcard *:	=AVERAGEIF(C3:C13,"*Asia",D3:D13) // ends with "Asia"
Wildcard ?:	=AVERAGEIF(C3:C13,"?????",D3:D13) // contains 5 characters
Cell reference:	=AVERAGEIF(C3:C13,"<>"&A2,D3:D13) // not equal to value of cell A2

Caveats

AVERAGEIF function:

• can only apply a single criterion.
• ignores empty cells, even when criteria match.
• returns #DIV/0! if no cells in range meet criteria
• use the * and ? characters for wildcards. To find an actual question mark or asterisk, type a tilde (~) preceding the character.
• logical operators (<,>,<>,=) can be used for partial matches and should be enclosed in double quotation marks "" along with non-numeric criteria being evaluated (quotation marks are not required for numeric values).
• cell references in criteria are not enclosed in quotes, i.e. "<"&A1
  
  

---

Related Functions

• AVERAGEIFS: average values in a range that meet specific or multiple criteria.
• SUMIF: sum (add) values in a range that meet a single criterion.
• SUMIFS: sum (add) values in a range that meet specific or multiple criteria.
• COUNTIF count values within a range that meet one specific criteria.
• COUNTIFS count values within a range that meet specific or multiple criteria.

Functions Category

• Statistical