What is the function to calculate the average of only the values that meet certain criteria?

In statistical term average means arithmetic mean or mean of numbers provided. It is calculated by summing up a set of numbers and then divided by count of those numbers. In simple words, average means to find out the most common value in a set of numbers. Excel provides the special function to calculate an average of a provided set of numbers.

But what about calculating the average of only those numbers in a set of numbers who meet certain condition or criteria? For this scenario, Excel has a built-in function AVERAGEIF.

AVERAGEIF function in Excel returns the average or arithmetic mean of all those numbers in a set or range of cells who meet specified criteria.

Syntax of AVERAGEIF in Excel is;

AVERAGEIF( range, criteria, [average_range] )

Where,

range – The range of cells where you need to test the condition or criteria

– The condition used to determine which cells to average. It is supplied as a number, text value, the logical expression, or a cell reference containing the condition to meet.

average_range – The range of cells that contain numbers which you want to average. It is optional and if you omit this to supply in function then function calculates the average of values given in range argument.

There are some points to be remembered while using AVERAGEIF function in Excel.

• Empty cells in range and average_range arguments are ignored.
• Cells containing logical values (TRUE and FALSE) in range argument are ignored while calculating the average.
• AVERAGEIF function supports using wildcard characters question marks (?) and asterisk (*) in criteria argument for partially criteria matches.
• If none of the values in range meets specific criteria or range is blank or range contains text values, AVERAGEIF function returns error value #DIV/0!

In this article, you need to learn how to use AVERAGEIF function in Excel to calculate the average of cells based on various conditions.

Average cells for the exact match

In this case, AVERAGEIF function calculates the average of those cells that exactly meet the specified criteria. For example, you need to calculate the average value of cells where a year is exactly 2015, such as;

=AVERAGEIF(A2:A9,2015,B2:B9)

This formula considers those values in range B2:B9 for calculating average where the year is exactly matched to 2015 in range A2:A9.

Average cells for partial criteria match

Using AVERAGEIF function you can take the average of values based on partial criteria match. You need to use wildcard characters, question mark (?) and asterisk (*), in the criteria argument as per following match types;

• Use the question mark (?) to match any single character.
• Use the asterisk mark (*) to match any sequence of characters.

Suppose you need to calculate average sales figure of different types of a particular item “Apple”. In this case, you need to use asterisk wildcard (*) in criteria argument with this item to calculate average where any sequence of the item is present in cell range.

=AVERAGEIF(A2:A9,"*Apple",B2:B9)

Average cells based on numeric criteria

Using AVERAGEIFfunction you can easily calculate the average of only those numeric values that are Greater Than (>) or Greater Than Equal to (>=) or less Than (<) or Less Than Equal to (<=)  to the certain specified number using a logical expression in criteria argument, such as;

=AVERAGEIF(A2:A9,">=15")

This formula calculates the average of those values that are Greater Than Equal to 15, using the logical expression “>=15”.

Average cells based on Date criteria

In Excel, you might face a situation where you need to calculate the average of cells’ values where their corresponding cells contain dates and you need to test date as criteria to take the average of cells that fall in that date criteria.

Suppose you have a data where orders has been delivered on various dates. So you can calculate the average of orders’ amounts that are delivered on or after a specified date.

To calculate average sales figure of orders where the date is equal to or greater than a given date as criteria then you need to apply AVERAGEIF function, such as;

=AVERAGEIF(B2:B9,">="&E2,C2:C9)

OR

=AVERAGEIF(B2:B9,">="&DATE(2018,8,15),C2:C9)

In this case, the date is specified as criteria in cell reference (E2) or in DATE function along with logical expression Greater Than Equal to (“>=”), joined together by Ampersand (&), a concatenating operator. Logical expression is supplied in double quotation mark but date cell reference or DATE function is supplied without double quotation mark in the criteria argument of AVERAGEIF function.

OR