AVERAGEIFS
Formulas / AVERAGEIFSCalculate the average of cells that satisfy multiple criteria.
=AVERAGEIFS(avg_rng, range1, criteria1, [range2], [criteria2], ...)
- avg_rng - the range of cells to average
- range1 - the first range to apply the filtering criteria
- criteria1 - the filtering criteria to use on range1
- range2 - [OPTIONAL] the second range to apply the filtering criteria
- criteria2 - [OPTIONAL] the criteria to use on range2 to filter the rows
Examples
=AVERAGEIFS(C5:C15,C5:C15,">0")returns the average price of properties greater than zero. This formula looks at the range of cells C5 to C15 and averages all of the values that are greater than zero.=AVERAGEIFS(C5:C15,D5:D15,">=2",E5:E15,">1")returns the average price of properties with more than 1 bathroom and at least 2 bedrooms. This formula looks at the range of cells C5 to C15 and averages all of the values that meet the criteria of having more than 1 bathroom and at least 2 bedrooms.=AVERAGEIFS(C5:C15,C5:C15,">0",C5:C15,"<500000")returns the average price of properties greater than zero and less than $500,000. This formula looks at the range of cells C5 to C15 and averages all of the values that meet the criteria of being greater than zero and less than $500,000.
Summary
The AVERAGEIFS function calculates the arithmetic mean of cells matching multiple criteria. Cells in the average_range which aren't numbers will return a #DIV/0! error, and empty cells in the criteria range will be treated as 0.
- AVERAGEIFS averages a set of cells using logical operators, wildcards, dates, text values, and numbers as criteria.
- If no cells meet criteria, AVERAGEIFS will return a #DIV/0! error. Empty cells are ignored when computing the average.
- If ranges are not the same size, AVERAGEIFS returns #VALUE! error.
- Only cells that match the criteria are used when computing the average. TRUE and FALSE are ignored.
- AVERAGEIFS cannot accept an array and ignores case.
Frequently Asked Questions
What is the syntax for the AVERAGEIFS function?
The syntax for AVERAGEIFS is AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...).
What is the required argument for the AVERAGEIFS function?
The required arguments for AVERAGEIFS are:
- average_range - a set of cells to average
- criteria_range1 - a set of cells to evaluate the first criteria in
- criteria1 - a set of cells to evaluate the remaining criteria
Are there any optional arguments for the AVERAGEIFS function?
Yes, the optional arguments for AVERAGEIFS are criteria_range2, criteria2, ... which are additional criteria ranges to evaluate additional criteria in.
