Syntax
The syntax for the AVERAGEIFS function depends on the criteria being evaluated. Each separate condition will require a range and a criteria. The generic syntax for SUMIFS looks like this: 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 values 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 in order to get a value, before that value can be joined with an operator.
Examples
In the example shown, the formulas in H5:H7 are: These formulas return the average price of properties where:
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, the number and 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. 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:
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:
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.
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: The tilde (~) is an escape character to allow you to find literal wildcards. For example, to match a literal question mark (?), 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 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
