AVERAGEIF

The AVERAGEIF function calculates the average of a range of cells that meet a specified condition. It is commonly used to find the average of values in a range based on a given criterion or condition.

Syntax

=AVERAGEIF(range, criteria, [average_range])

range The range of cells that you want to evaluate.
criteria The criteria or condition that determines which cells to include in the average.
average_range (Optional) The actual range of cells to average. If omitted the range specified in the 'range' argument is used for averaging.
range The actual range of cells for evaluation.

About AVERAGEIF

When you need to determine the average of specific data points that satisfy certain conditions or criteria, the AVERAGEIF function in Excel steps in as your trusty ally. It offers a straightforward approach to calculating the average of values within a defined range, based on a user-specified condition or criteria. This functionality proves particularly useful in various data analysis and reporting scenarios, enabling swift identification of average values that align with specific filters or conditions set by the user across the given dataset.

To harness the power of AVERAGEIF, simply specify the range of cells to be evaluated and define the criteria that need to be met for inclusion in the average calculation. Optionally, you can specify a separate range of cells for which the average is to be calculated, providing flexibility to tailor the calculation according to your specific requirements. AVERAGEIF operates with ease across both numeric and non-numeric datasets, delivering efficiency and accuracy in equal measure, elevating your data analysis endeavors to new heights of precision and insight.

In essence, AVERAGEIF emerges as a dependable tool in your Excel arsenal, streamlining the task of deriving average values from a dataset based on defined conditions. Whether it's analyzing sales figures meeting specific criteria, or averaging measurements falling within designated ranges, AVERAGEIF stands ready to simplify your data analysis pursuits with its intuitive functionality and unwavering reliability.

Examples

Suppose you have a list of test scores in cells A1:A10 and you want to find the average of scores that are greater than 70. The AVERAGEIF formula would be:

=AVERAGEIF(A1:A10, ">70")

This will return the average of scores that are greater than 70.

Suppose you have a list of sales amounts in cells B1:B20 and a corresponding list of products in cells A1:A20. You want to find the average sales amount for a specific product, for example, 'Product A'. The AVERAGEIF formula would be:

=AVERAGEIF(A1:A20, "Product A", B1:B20)

This will return the average sales amount for 'Product A'.

Questions

What happens if the criteria specified in AVERAGEIF does not match any cells in the range?

If the specified criteria in AVERAGEIF does not match any cells in the range, the function returns the #DIV/0! error, indicating that a division by zero (no matching cells) has occurred.

Can the AVERAGEIF function handle criteria involving text-based comparisons?

Yes, the AVERAGEIF function can handle criteria involving text-based comparisons, allowing for the calculation of the average based on specific text criteria or conditions within the given range.

Is it possible to use multiple criteria with the AVERAGEIF function?

No, the AVERAGEIF function in Excel only supports a single criteria. For scenarios requiring multiple criteria, the AVERAGEIFS function or alternative methods may be employed.

Related functions

AVERAGEIFS COUNTIF SUMIF SUMIFS