AVERAGEIF

Excel Functions

Get the average of numbers that meet criteria.

Syntax

EXCEL

=AVERAGEIF(range, criteria, [average_range])

Arguments

Parameter	Description
`range`	One or more cells, including numbers or names, arrays, or references.
`criteria`	A number, expression, cell reference, or text.
`average_range`	[optional] The cells to average. When omitted, range is used.

Return Value

A number representing the average.

Details

The AVERAGEIF function calculates the average of the numbers in a range that meet supplied criteria. To apply criteria, the AVERAGEIF function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. AVERAGEIF can be used to average cells based on dates, numbers, and text. Note that AVERAGEIF only handles one condition. To apply multiple conditions, use the AVERAGEIFS function. The generic syntax for AVERAGEIF looks like this: The AVERAGEIF function takes three arguments: range, criteria, and average_range. Range is the range of cells to apply a condition to, and criteria is the condition to apply, along with any logical operators that are needed. Average_range argument is optional. When average_range is not provided, AVERAGEIF will average values in the range argume

Examples

Syntax

The generic syntax for AVERAGEIF looks like this:

EXCEL

=AVERAGEIF(range,criteria,[average_range])

Examples

In the example shown, the formulas in H5:H8 are as follows:

EXCEL

=AVERAGEIF(C5:C15,">0") // price greater than $0
=AVERAGEIF(C5:C15,">200000") // price greater than $200k
=AVERAGEIF(D5:D15,">=2",C5:C15) // 2+ bedrooms
=AVERAGEIF(D5:D15,">=3",C5:C15) // 3+ bedrooms

Double quotes ("") in criteria

In general, text values are enclosed in double quotes (""), and numbers are not. However, when a logical operator is included with a number, the numbe

EXCEL

=AVERAGEIF(D5:D15,2,C5:C15) // 2 bedrooms
=AVERAGEIF(D5:D15,">=2",C5:C15) // 2+ bedrooms

Double quotes ("") in criteria

Double quotes are also used for text values. For example, to average values in B1:B10 when values in A1:A10 equal "red", you can use a formula like th

EXCEL

=AVERAGEIF(A1:A10,"red",B1:B10) // average "red" only

Value from another cell

A value from another cell can be included in criteria using concatenation. In the example below, AVERAGEIF will return the average of numbers in A1:A1

EXCEL

=AVERAGEIF(A1:A10,"<"&B1) // average values less than  B1

Wildcards

The wildcard characters question mark (?), asterisk(), or tilde (~) can be used in criteria. A question mark (?) matches any one character, and an ast

EXCEL

=AVERAGEIF(A1:A10,"*red*",B1:B10) // contains "red"

AVERAGEIF

Syntax

Arguments

Return Value

Details

Examples

See Also