Excel AVERAGEIFS function

Summary

The Excel AVERAGEIFS function extends the capabilities of AVERAGEIF by allowing you to compute the average (arithmetic mean) of all numbers in a range that meet multiple criteria. This function is invaluable for complex data analysis tasks, such as averaging sales figures for a specific product within a particular region over a certain period.
Syntax
				
					=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
				
			
  • average_range: The range of cells you want to average.
  • criteria_range1: The range of cells to evaluate with the first criteria.
  • criteria1: The condition that must be met in criteria_range1.
  • [criteria_range2, criteria2], …: Additional ranges and their associated criteria. Each criteria_range must be the same size and shape as average_range.
Return value
The average of the selected cells that meet all the specified criteria.

How to use

To use the AVERAGEIFS function, specify the range to average and at least one pair of criteria range and criteria. You can include multiple criteria pairs to refine the selection of cells to be averaged further. Each criteria pair filters the average_range more specifically, and only cells that meet all conditions are included in the final average calculation.

Examples

Simple AVERAGEIFS
To Compute an Average Based on a Single Condition: If you’re looking to find the average sales amount for sales over $500 in A1:A10.
				
					=AVERAGEIFS(A1:A10, A1:A10, ">500")
				
			
Assuming A1:A10 contains sales amounts, and three of these amounts are over $500, AVERAGEIFS calculates the average of just those three amounts.
AVERAGEIFS for Multiple Conditions
Averaging Sales in a Specific Region for a High-Quality Product: To calculate the average sales amount for a “High” quality product in the “East” region.
				
					=AVERAGEIFS(B1:B10, A1:A10, "East", C1:C10, "High")
				
			
If B1:B10 holds sales amounts, A1:A10 identifies regions with several marked “East,” and C1:C10 rates product quality, with some marked as “High,” the formula computes the average sales for products in the East region that are rated High.
AVERAGEIFS with Date Range
Calculating the Average Sales for a Particular Month: To find the average sales in March 2022.
				
					=AVERAGEIFS(B1:B10, D1:D10, ">=3/1/2022", D1:D10, "<=3/31/2022")
				
			
Here, B1:B10 contains sales amounts, and D1:D10 holds the sale dates. This formula averages sales amounts for all dates falling in March 2022.
AVERAGEIFS for Text Criteria
Averaging Based on Employee Performance: To calculate the average bonus for employees rated as “Excellent”.
				
					=AVERAGEIFS(B1:B10, C1:C10, "Excellent")
				
			
Assuming B1:B10 has bonus amounts and C1:C10 contains performance ratings, this formula averages bonuses for those rated “Excellent”.
AVERAGEIFS with Wildcards
Identifying Average Sales for Products with Similar Names: To find the average sales for products that start with “Pro”.
				
					=AVERAGEIFS(B1:B10, A1:A10, "Pro*")
				
			
In this case, A1:A10 lists product names, some of which begin with “Pro”, and B1:B10 contains the corresponding sales amounts. The formula calculates the average sales for these products.

Additional Notes

  • AVERAGEIFS requires that each criteria_range be the same size as the average_range.
  • Logical operators (such as >, <, >=, <=, <>) can be used within criteria, and wildcards ( for multiple characters, ? for single characters) are allowed for text criteria.
  • AVERAGEIFS does not average cells that are empty, contain text, or logical values unless directly matching the criteria.

Related Functions

Excel AVERAGEIF function

The Excel AVERAGEIF function computes the average of numbers based on a criterion, perfect for conditional averaging.

Excel AVERAGE function

The Excel AVERAGE function calculates the mean of numbers, essential for summarizing central tendencies in data sets.

Content Navigation