AVERAGEIFS

The AVERAGEIFS function calculates the average of a range of values based on multiple criteria. It allows you to specify one or more sets of criteria and determine the average of cells that meet all of the specified conditions.

Syntax

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])

average_range The range of cells to average based on the specified criteria.
criteria_range1 The range of cells that the first criterion will be applied to.
criteria1 The criterion or condition to be met in the first criteria_range.
criteria_range2 (Optional) Additional ranges to which further criteria will be applied.
criteria2 (Optional) Additional criteria to be met in the corresponding criteria_range.
... Additional pairs of criteria_range and criteria can be added as needed.

About AVERAGEIFS

When you need to determine the average of a range of data while considering specific conditions, the AVERAGEIFS function in Excel comes to the rescue. It empowers you to tailor your average calculation by applying multiple criteria, providing a more granular and targeted outcome. This functionality is particularly valuable in scenarios where you want to analyze data based on different characteristics or attributes, allowing for deeper insights into your dataset's subsets. The AVERAGEIFS function becomes your trusty companion, effortlessly sifting through your data to deliver the precise average you seek, guided by the criteria you define. With its flexibility and versatility, AVERAGEIFS becomes an indispensable tool in your Excel arsenal, enabling you to derive meaningful averages that align with your specific requirements.

Examples

Suppose you have a dataset containing sales information for different products, and you want to calculate the average sales amount for a particular product category (criteria1) during a specific month (criteria2). You can use the AVERAGEIFS formula as follows: =AVERAGEIFS(sales_amount_range, product_category_range, "Electronics", month_range, "January") This will provide the average sales amount for the Electronics category in the month of January based on the specified criteria.

In another scenario, you have data for student scores across various subjects, and you wish to determine the average score for a specific grade level (criteria1) and in a particular exam type (criteria2). You can employ the AVERAGEIFS function in the following manner: =AVERAGEIFS(score_range, grade_level_range, "10th Grade", exam_type_range, "Midterm") This will yield the average score for the 10th grade students specifically in the Midterm exam.

Questions

What happens if not all of the criteria specified in AVERAGEIFS are met for a particular cell?

If any of the specified criteria are not met for a cell, that cell's value will be excluded from the average calculation. AVERAGEIFS only includes cells that meet all of the specified conditions.

Can I specify multiple sets of criteria in the AVERAGEIFS function?

Yes, AVERAGEIFS allows you to specify multiple sets of criteria by providing additional pairs of criteria_range and criteria. This enables you to further refine the subsets of data for which you want to calculate the average.

What is the difference between AVERAGEIFS and AVERAGEIF?

While AVERAGEIFS allows for the application of multiple criteria to determine the average, AVERAGEIF focuses on a single criterion. AVERAGEIF calculates the average based on a single condition, whereas AVERAGEIFS enables the use of multiple conditions to refine the average calculation.

Related functions

AVERAGEIF
SUMIFS
COUNTIFS
MAXIFS
MINIFS