How to Use the AVERAGEIF Formula with a Condition in Excel

To calculate the average value based on a condition in Excel, you can use the AVERAGEIF function. Along with summing and counting values, calculating an average based on a condition is one of the most commonly performed operations with cell ranges in Excel. Therefore, it's essential to learn how to efficiently use such functions.



How to Find the Average Value Based on a Condition in Excel

Below is an image of the list of medalists from the 1972 Winter Olympics. Let’s say we need to find the average results of the medalists from Switzerland. The country name for the condition is written in a separate cell, making it easy to change to another. The solution formula is:

=AVERAGEIF(C2:C19,H1,D2:D19)
AVERAGEIF Formula.

To solve this problem, Excel offers the special AVERAGEIF function. Its working principle is very similar to the related SUMIF function for summing based on a condition. It also uses the same required arguments: Range and Criteria. The only difference is the optional argument: Average_range.

In this example, each value in the range … is checked to determine whether it will be considered when calculating the average after the selection. It all depends on whether the current cell’s value meets the selection criteria specified in the condition. If no cell satisfies the condition, the AVERAGEIF function returns the error #DIV/0!

Alternative Formula for Calculating the Average Value with a Condition

If desired, you can create an alternative formula without using the AVERAGEIF function. In mathematics, the average value is called the arithmetic mean—it is the sum of all values divided by their count. Similarly, the AVERAGEIF function sums all values in the range specified in the first argument, considering the selection criteria specified in the second argument. The result is then divided by the count of selected values. You can achieve the same result by using a combination of two related functions, dividing the SUMIF result by the COUNTIF result. Thus, to calculate the average value based on the analysis of the table above, you can use an alternative formula:

=SUMIF(C2:C19,H1,D2:D19)/COUNTIF(C2:C19,H1)
Alternative Average Formula.

Download examples of how to use the AVERAGEIF function in Excel download file

In Excel, 90% of tasks can be solved with multiple correct solutions.