AVERAGEIFS

Excel Functions

Average cells that match multiple criteria

Syntax

EXCEL

=AVERAGEIFS(avg_rng, range1, criteria1, [range2], [criteria2], ...)

Arguments

Parameter	Description
`avg_rng`	The range to average.
`range1`	The first range to evaluate.
`criteria1`	The criteria to use on range1.
`range2`	[optional] The second range to evaluate.
`criteria2`	[optional] The criteria to use on range2.

Return Value

The average of the cells that meet all criteria

Details

The AVERAGEIFS function calculates the average of cells in a range that meet multiple conditions, referred to as criteria. Each condition is provided with a separate range and criteria. To define criteria, AVERAGEIFS supports various logical operators (>,<,<>,=) and wildcards (*,?,~). The AVERAGEIFS function is widely used in Excel and can be used to average cells based on dates, text values, and numbers. However, the syntax used to apply conditions is a bit tricky because it is unusual in Excel. See below for details. The syntax for the AVERAGEIFS function depends on the criteria being evaluated. Each separate condition will require a range and criteria. The generic syntax for AVERAGEIFS looks like this: The first argument, avg_range, is the range of cells to average, which should contain

Examples

Syntax

The syntax for the AVERAGEIFS function depends on the criteria being evaluated. Each separate condition will require a range and criteria. The generic

EXCEL

=AVERAGEIFS(avg_range,range1,criteria1) // 1 condition
=AVERAGEIFS(avg_range,range1,criteria1,range2,criteria2) // 2 conditions

Examples

In the example shown, the formulas in H5:H7 are:

EXCEL

=AVERAGEIFS(C5:C15,C5:C15,">0")
=AVERAGEIFS(C5:C15,C5:C15,">0",C5:C15,"<500000")
=AVERAGEIFS(C5:C15,D5:D15,">=2",E5:E15,">1")

Double quotes ("") in criteria

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

EXCEL

=AVERAGEIFS(C5:C15,D5:D15,2) // 2 bedrooms
=AVERAGEIFS(C5:C15,D5:D15,">=2") // 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

=AVERAGEIFS(B1:B10,A1:A10,"red")

Multiple criteria

Enter criteria in pairs [range, criteria]. For example, to average values in A1:A10, where B1:B10 = "A", and C1:C10 > 5, use:

EXCEL

=AVERAGEIFS(A1:A10,B1:B10,"A",C1:C10,">5")

Value from another cell

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

EXCEL

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

AVERAGEIFS

Syntax

Arguments

Return Value

Details

Examples

See Also