What is the Excel AVERAGEIFS Function?
The Excel AVERAGEIFS function is a solution for calculating an average (arithmetic mean) of values supplied that meet more than one criteria. The AVERAGEIFS function is an improvement from the excel AVERAGEIF function provided since Excel 2007.
AVERAGEIFS Syntax
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
average_range, required, the range to average
criteria_range1, required, the first range to be evaluated by the criteria1
criteria1, required, the first criteria to evaluate on criteria_range1.
[criteria_range2], optional, the second range to be evaluated by the criteria2. [criteria2], optional, the second criteria to evaluate on criteria_range1.Usage Notes:
- AVERAGEIFS function can handle up to 127 range/criteria_range pairs.
- AVERAGEIFS function returns the #DIV0! error when no criteria are meet or average_range is a blank or text value.
- AVERAGEIFS function treats empty cell in criteria_range as a 0 (zero) value.
- AVERAGEIFS function evaluate TRUE as 1 and FALSE as 0 (zero) in range argument.
- AVERAGEIFS function does an average calculation in average_range if all criteria specified for that cell are meet.
- AVERAGEIFS criteria_range must have the same number of rows and columns as average_range
- AVERAGEIFS function allows the wildcard characters asterisk (*) and question mark (?) in criteria. An asterisk wildcard for any character sequence, and a question mark wildcard for any single character; use a tilde (~) before character to find actual asterisk and question mark.
How to Use AVERAGEIFS Function in Excel
For example, there is data as shown below. How to create an excel formula for the following questions:
Question #1, AVERAGEIFS Multiple Criteria in Same Column (AND Criteria)
The Question
What are the average sales for an iPhone with storage of less than 256GB and more than 32GB?
The Criteria
There are two criteria, criteria #1 storage is less than 256GB, criteria #2 storage is more than 32GB, both point to the same column. All criteria specified must be met to make excel do the average calculation.
The Formula
=AVERAGEIFS(D2:D14,C2:C14,"<256",C2:C14,">32")
Criteria_range1 and criteria_range2 point to the same range address. The result is 7,312.
Question #2, AVERAGEIFS Multiple Criteria in Same Column (OR Criteria)
The Question
What are the average iPhone sales for the “West” and “East” area?
The Criteria
There are two criteria, criteria #1 area “West”, criteria #2 area “East”, both point to the same column.
**
Is it possible, in one cell there is a “West” and “East” area together? Certainly impossible. The average calculation for the above questions will be done if ONE OF THE CRITERIA IS MEET.
Even though the AVERAGEIFS function only performs an average calculation if all criteria are met. The above question cannot be answered using the AVERAGEIFS function. Read “AVERAGEIFS Limitations” below for a solution.
Read “AVERAGEIFS Limitations” below for a solution.
Question #3, AVERAGEIFS Multiple Criteria in Different Column (AND Criteria)
The Question
What are the average iPhone sales for the “plus” variant in the “East” area?
The Criteria
There are two criteria, criteria #1 a “plus” variant name point to column B, criteria #2 “East” area point to column A.
The Formula
=AVERAGEIFS(D2:D14,B2:B14,"*plus",A2:A14,"east")
The result is 7,192
Question #4, AVERAGEIFS Multiple Criteria in Different Column (OR Criteria)
The Question
What are the average iPhone sales for a “plus” variant OR the storage capacity equal to 64GB?
A weird question, but this is only to show you a question with criteria in a different column and only one of the criteria must be met.
The Criteria
There are two criteria, criteria #1 a “plus” variant name point to column B, criteria #2 storage equal to 64GB point to column C.
**
Like question #2, only one criterion is needed to do the average calculation and cannot be solved with the AVERAGEIFS function.
Read “AVERAGEIFS Limitations” below for a solution.
AVERAGEIFS Limitations
The AVERAGEIFS function limitation is to answer questions that only require one criterion that is fulfilled from several existing criteria.
There are two solutions. The first solution is using a helper column containing the OR function and the second solution is using an array formula.
For more details, read the following article for a reference. It’s a different function, but you can use the same trick to solve the AVERAGEIFS function limitations.
