Math & Statisticsintermediate
Average values by multiple criteria with AVERAGEIFS
=AVERAGEIFS(D2:D100,B2:B100,"East",C2:C100,"Q1")
Function reference
AVERAGEIFS in Excel & Google Sheets
AVERAGEIFS is used in 1 formula pattern in this library. Browse worked examples below, or use the generator to build a custom variation for your specific data.
1 intermediateBuild a custom AVERAGEIFS formula →
When to use AVERAGEIFS
- Find the average order value for a specific region and product category, or the average score for a department in a given quarter.
Worked examples (1)
Common errors with AVERAGEIFS
AVERAGEIFS returns
No rows matched all the criteria, so there are no values to average. Check that the criteria values match the data exactly, including capitalisation and trailing spaces.
Criteria range and average range are different sizes
All ranges in AVERAGEIFS must have the same number of rows. Mismatched ranges cause a #VALUE error.
Frequently asked questions
Can I use comparison operators in AVERAGEIFS criteria?
Yes — use text strings like ">100" or "<="&A1 as the criteria argument. For example, AVERAGEIFS(D2:D100,C2:C100,">="&DATE(2026,1,1)) averages values with a date on or after Jan 1 2026.
Need a custom variation?
Describe your specific AVERAGEIFS problem and get a formula written for your exact data.
Open generator with AVERAGEIFS