AVERAGEIFS Function

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.

Syntax

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:

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

The first argument, avg_range, is the range of cells to average, which should contain numeric values. The second argument, range1, is the range to which the first condition should be applied. The third argument, criteria1, contains the condition that should be applied to range1, along with any logical operators. Additional conditions are applied by providing additional range/criteria arguments. When using AVERAGEIFS, keep the following in mind:

• Only cells that meet all conditions will be included in the final result.
• All ranges must be the same size or AVERAGEIFS will return a #VALUE! error.
• AVERAGEIFS will not include empty cells in the average, even when criteria match.
• AVERAGEIFS will return a #DIV/0! error if no cells meet criteria.

Applying criteria

The AVERAGEIFS function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Because AVERAGEIFS is in a group of eight functions that split logical criteria into two parts, the syntax is a bit tricky. Each condition requires a separate range and criteria, and operators need to be enclosed in double quotes (""). The table below shows some common examples:

Notice the last two examples use concatenation with the ampersand (&) character. When a criteria argument includes a value from another cell, or the result of a formula, logical operators like "<" must be joined with concatenation. This is because Excel needs to evaluate cell references and formulas first to get a value, before that value can be joined with an operator.

Examples

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

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

These formulas return the average price of properties where:

1. prices are greater than zero
2. prices are greater than zero and less than $500,000
3. properties have at least 2 bedrooms and more than 1 bathroom

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 number, both the number and the operator must be enclosed in quotes. Note the difference in the two examples below. Because the second formula uses the "greater than or equal to" operator (>=), the operator and number are both enclosed in double quotes.

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

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 this:

=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:

=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:A10 that are less than the value in cell B1. Notice the less than operator (which is text) is enclosed in quotes.

=AVERAGEIFS(A1:A10,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 asterisk (*) matches zero or more characters of any kind.  For example, to average values in B1:B10 when values in A1:A10 contain the text "red", you can use a formula like this:

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

The tilde (~) is an escape character to match literal wildcards. For example, to match a literal question mark (?), an asterisk (*), or tilde (~), add a tilde in front of the wildcard (i.e. ~?, ~*, ~~).

Note: the order of arguments is different between AVERAGEIFS and AVERAGEIF. The range to average is always the first argument in AVERAGEIFS.

Notes

• All ranges must be the same size or AVERAGEIFS will return a #VALUE! error.
• Only values that meet all conditions will be included in the final result.
• AVERAGEIFS will not include empty cells in the average, even when criteria match.
• TRUE and FALSE values are ignored when calculating an average.
• AVERAGEIFS will return a #DIV/0! error if no cells meet criteria.
• AVERAGEIFS requires a range, you can't substitute an array.
• Logical operators and text values should be enclosed in double quotes ("").
• AVERAGEIFS supports wildcards but is not case-sensitive.