AVERAGEIFS
Returns the average of cells that meet multiple criteria.
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)Arguments
average_rangeThe range to averagecriteria_range1First range to evaluatecriteria1First criteriacriteria_range2Additional range(optional)criteria2Additional criteria(optional)
=AVERAGEIFS(C:C,A:A,"Sales",B:B,">1000")Average with two conditions
=AVERAGEIFS(Scores,Team,"A",Year,2024)Average for specific team and year
=AVERAGEIFS(B:B,A:A,">="&D1,A:A,"<="&D2)Average within date range
- •Use wildcards * and ? in text criteria
- •All ranges must be same size
- •Returns #DIV/0! if no matches
- •Putting average_range in the wrong argument position - unlike AVERAGEIF, AVERAGEIFS requires average_range first, before any of the criteria pairs, and reversing this breaks the formula
- •Supplying criteria ranges of different sizes than average_range, which raises a #VALUE! error since every range must have matching dimensions
- •Assuming AVERAGEIFS supports an OR relationship between criteria - it always requires ALL conditions to be true simultaneously (AND logic), so an OR-style average needs a different approach, like SUMPRODUCT or adding two AVERAGEIFS results
In what order do the arguments go in AVERAGEIFS?
average_range comes first, followed by pairs of criteria_range and criteria - the opposite order from AVERAGEIF, where average_range is optional and comes last.
Can AVERAGEIFS average values that match ANY of several conditions instead of ALL of them?
Not directly - AVERAGEIFS always applies AND logic across all criteria. For an OR condition, you'd typically sum two separate AVERAGEIFS-style calculations or use SUMPRODUCT.
Why do I get a #VALUE! error with AVERAGEIFS?
One of your criteria ranges likely has different dimensions than average_range - every range in the formula must cover the same number of rows and columns.
Need to translate a formula using AVERAGEIFS?
Use our translator to convert your complete formula