NumstroBuilderExplainerFunctionsAbout

The AVERAGEIFS function calculates the average of values that meet multiple criteria. This guide explains how AVERAGEIFS works and includes practical examples you can use immediately.

WHAT THE AVERAGEIFS FUNCTION DOES

AVERAGEIFS computes the mean of values that satisfy multiple conditions simultaneously, using AND logic — all criteria must be met for a value to be included. It's an extension of AVERAGEIF that allows you to filter by multiple dimensions, such as averaging sales for a specific region and product combination.

WHEN TO USE AVERAGEIFS

Use AVERAGEIFS when you want to:

  • Average values with multiple filters
  • Apply multiple conditions to your data
  • Exclude data that doesn't meet all conditions
FORMULA SYNTAX
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
ArgumentDescription
average_rangeRange containing numbers to average
criteria_range1Range to test against first condition
criteria1The first condition to match
criteria_range2(optional) Another range to test
criteria2(optional) Second condition to match
EXAMPLE 1: ONE CONDITION

Average all scores where the department is Sales.

ABC
1DepartmentRegionScore
2SalesWest85
3SalesEast92
4MarketingWest88
5SalesWest79
=AVERAGEIFS(C2:C5, A2:A5, "Sales")

Result: (85 + 92 + 79) / 3 = 85.33

Need help applying this? Try this in Numstro Builder

EXAMPLE 2: MULTIPLE CONDITIONS

Average total spend of customers who are located in the West region and have purchased more than $1,000.

ABC
1CustomerRegionTotal Spend
2AliceWest1200
3BobWest900
4CarolEast1300
5DavidWest1500
=AVERAGEIFS(C2:C5, B2:B5, "West", C2:C5, ">1000")

Result: (1200 + 1500) / 2 = 1350

Need help applying this? Try this in Numstro Builder

COMMON MISTAKES & EDGE CASES
  • All ranges must be the same size — mismatched ranges return #VALUE!
  • If no matches are found, the result is #DIV/0!
  • Quotes are required for text or logical criteria: "Sales", ">70"
  • Blank cells in average_range are ignored
RELATED FUNCTIONS
  • AVERAGEIF — average with one condition
  • COUNTIFS — count with multiple conditions
  • SUMIFS — sum with multiple conditions
FURTHER READING

Official Microsoft documentation:
AVERAGEIFS — Microsoft Support