Excel AVERAGEIF function

Summary

The Excel AVERAGEIF function calculates the average (arithmetic mean) of all numbers in a range that meet a specified criterion. This function streamlines the process of conditional averaging, making it ideal for scenarios such as calculating the average sales for a specific product, averaging grades above a certain threshold, or determining average expenses within a particular category.
Syntax
				
					=AVERAGEIF(range, criteria, [average_range])
				
			
  • range: The range of cells you want to evaluate with the criteria.
  • criteria: The condition that determines which cells to average. This can be a number, expression, text, or even a cell reference that defines which cells will be included in the average.
  • [average_range]: [Optional] The actual set of cells to average. If omitted, the cells in range are averaged.
Return value
The average of the selected cells that meet the specified criteria.

How to use

To use the AVERAGEIF function, specify the range of cells to evaluate, the criteria for including cells in the average, and optionally, a different range of cells to calculate the average. The function then processes only those cells in the specified range that meet the criteria, calculating the average of either those same cells or a corresponding set of cells in the average_range.

Examples

Simple AVERAGEIF
To Calculate the Average for Values Over a Certain Amount: If you want to find the average sales amount for transactions over $500.
				
					=AVERAGEIF(A1:A10, ">500")
				
			
Assuming A1:A10 contains transaction amounts and several of these exceed $500, AVERAGEIF finds the average of just those transactions over $500.
AVERAGEIF for Specific Text
Averaging Scores for a Specific Project: To compute the average score for “Project X”.
				
					=AVERAGEIF(B1:B10, "Project X", C1:C10)
				
			
If B1:B10 lists project names with multiple instances of “Project X” and C1:C10 contains corresponding scores, this formula calculates the average score for all instances of “Project X”.
AVERAGEIF with Date Criteria
Calculating the Average Sales in a Specific Month: To find the average sales in the month of March.
				
					=AVERAGEIF(D1:D10, ">=3/1/2022", B1:B10)
				
			
Here, D1:D10 holds sale dates, and B1:B10 contains sales amounts. The formula averages sales for dates that fall in March 2022, as specified in the criteria.
AVERAGEIF for Excluding Zeros
Excluding Zero Values from Average Calculation: To compute the average of non-zero entries.
				
					=AVERAGEIF(A1:A10, "<>0")
				
			
If A1:A10 includes a mix of numbers including zeros, this formula calculates the average excluding any zeros, focusing only on non-zero numbers.

Additional Notes

  • The criteria can include logical operators (such as >, <, >=, <=, <>) for numerical comparisons, or wildcards ( for multiple characters, ? for a single character) for text criteria.
  • Text criteria, or criteria that include logical or mathematical symbols, must be enclosed in double quotes (“”).
  • If average_range is omitted, AVERAGEIF averages the numbers in the range itself that meet the specified criteria, which can be particularly useful for straightforward averaging tasks within a single data set.

Related Functions

Excel AVERAGE function

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

Excel AVERAGEIFS function

The Excel AVERAGEIFS function calculates the average of numbers that meet multiple criteria, enhancing detailed data analysis.

Content Navigation