Exceljet

Quick, clean, and to the point

Excel AVERAGEIFS Function

Excel AVERAGEIFS function
Summary 

The Excel AVERAGEIFS function calculates the average of numbers in a range that meet one or more criteria. The criteria used for AVERAGEIFS can include logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

Purpose 
Average cells that match multiple criteria
Return value 
The average of the cells that meet all criteria
Syntax 
=AVERAGEIFS (avg_rng, range1, criteria1, [range2], [criteria2], ...)
Arguments 
  • avg_rng - The range to average.
  • range1 - The first range to evaulate.
  • criteria1 - The criteria to use on range1.
  • range2 - [optional] The second range to evaluate.
  • criteria2 - [optional] The criteria to use on range2.
Usage notes 

The AVERAGEIFS function calculates the average of the numbers in a range that meet supplied criteria. The first range given to AVERAGEIFS is the range containing numbers to average. Criteria are supplied in pairs [range, criteria] and only the first pair is required. For each additional criteria, supply another range/criteria pair. The criteria given to AVERAGEIFS are applied with AND logic – all criteria must be true for a number to be included in the average.

Criteria can be supplied as numbers, strings, or references. For example, valid criteria could be 10, ">10", A1, or "<"&A1. Criteria can also include wildcards for partial matching.

AVERAGEIFS is in a group of eight functions in Excel that split logical criteria into two parts (range + criteria). As a result, the syntax used to construct criteria is different, and AVERAGEIFS requires a cell range for range arguments – you can't use an array.

AVERAGEIFS will automatically ignore empty cells, even when criteria match. In other words, AVERAGEIFS will not include empty cells in the average. AVERAGEIFS returns #DIV/0! if no cells in meet criteria.

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, 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.

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

  • If no data matches criteria, AVERAGEIFS returns the #DIV0! error 
  • Each additional range must have the same number of rows and columns as the average_range.
  • Non-numeric criteria needs to be enclosed in double quotes but numeric criteria does not. For example: 100, "100", ">32", "jim", or A1 (where A1 contains a number).
  • The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches zero or more characters of any kind.
  • To find a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables