Excel AVERAGEIFS Function

What is the AVERAGEIFS Formula in Excel?

The AVERAGEIFS formula in Excel is a powerful statistical function that allows you to calculate the average of a range of values based on multiple specified criteria or conditions. This function is particularly valuable when you need to find the average of a subset of data that meets specific criteria, as it lets you filter and refine your calculations.

AVERAGEIFS Requires Three Main Arguments:

• The Range to Average: This is the set of values you want to average based on your criteria.
• The First Criteria Range: This is the range where you set your first condition or criteria.
• The First Criteria: This is the value or expression that defines the first condition.

You can also add additional pairs of criteria ranges and criteria values, allowing you to apply multiple conditions simultaneously. Excel will then calculate the average of the values that meet all the specified criteria.

For example, you could use AVERAGEIFS to find the average sales revenue for a specific product category and a particular region, filtering the data based on these two conditions.

The AVERAGEIFS formula in Excel is a versatile tool for performing conditional averaging, making it easier to extract meaningful insights from your data by considering multiple criteria simultaneously. It's valuable in scenarios where you need to analyze and average data that meet specific conditions, providing a more tailored and accurate result.

AVERAGEIFS Formula in Excel Syntax

The AVERAGEIFS formula in Excel is used to calculate the average of a range of numbers that meet multiple specified criteria. It's a versatile function that allows you to filter and perform calculations on data based on specific conditions. Below is the syntax for the AVERAGEIFS function:

AVERAGEIFS Formula in Excel Arguments

Here are the arguments of the AVERAGEIFS function in Excel:

1. Range to Average (Average_range): This is the range of values that you want to calculate the average for. It's the dataset you are analyzing and where the function will average values based on the specified criteria.
2. First Criteria Range (Criteria_range1): This parameter defines the first range of cells where you set your first condition or criteria. The AVERAGEIFS function will evaluate the values in this range to see if they meet the criteria you specify.
3. First Criteria (Criteria1): This is the value or expression that defines the first condition. Excel will compare each cell in the "First Criteria Range" to this value or expression. If the condition is met, the corresponding value in the "Range to Average" will be included in the calculation.
4. [Optional] Second Criteria Range (Criteria_range2) and Second Criteria (Criteria2): You can add additional pairs of criteria ranges and criteria values to further refine your average calculation. For instance, if you want to average values based on two conditions simultaneously, you'd specify a second criteria range and its corresponding criteria.
5. [Optional] Additional Criteria Pairs: You can continue to add more criteria ranges and criteria values as needed to apply multiple conditions to your data. Each additional pair of criteria ranges and criteria values narrow down the selection of values to include in the average calculation.

AVERAGEIFS Formula in Excel Return Value

The AVERAGEIFS formula in Excel returns the average (mean) of a selected range of values based on one or more specified criteria or conditions. This function is exceptionally useful when you need to calculate an average for a subset of data that meets certain criteria, allowing for more targeted and customized analysis.

When you use AVERAGEIFS, you provide one or more sets of criteria ranges and criteria values. Excel then evaluates each cell in the criteria ranges to check if it meets the specified conditions. If a cell satisfies all the conditions, its corresponding value in the range of average is included in the calculation of the average. The function essentially filters the data, including only the values that meet your criteria, and then computes their average.

This function is versatile and commonly used in various scenarios. For example, you can use it to find the average sales of a particular product in a specific region, to calculate the average test scores for students who scored above a certain grade, or to determine the average monthly revenue for a specific period.

AVERAGEIFS Formula in Excel Examples

Basic Example:
Suppose you have a simple dataset that contains the names of students, their test scores, and the subjects they were tested on. You want to calculate the average score for a specific subject. Here's the data:

Let's say you want to calculate the average score for the "Math" subject. You can use the AVERAGEIFS function to do this. Here's the formula:

• C2:C8 represents the range of scores (the average_range).
• B2:B8 represents the range of subjects (the criteria_range1).
• "Math" is the criteria (the criteria1) for the "Math" subject. When you enter this formula in a cell, Excel will calculate the average of the scores for the "Math" subject. In this case, the result is $(85 + 78 + 95) / 3 = 86$.

Run this syntax/example in your spreadsheet for a better and clearer explanation.

AVERAGEIFS with Text Criteria:

The AVERAGEIFS function in Excel typically works with numeric criteria to calculate averages. However, you can still use AVERAGEIFS with text criteria by incorporating additional criteria ranges and criteria. Let's consider an example:

Suppose you have a dataset containing information about employees, including their names, departments, and salaries. You want to calculate the average salary for employees in a specific department, such as the "Marketing" department. Here's a sample dataset:

To calculate the average salary for employees in the "Marketing" department using AVERAGEIFS, you can do the following:

• C2:C8 represents the range of salaries (the average_range).
• B2:B8 represents the range of departments (the criteria_range1).
• "Marketing" is the criteria (the criteria1) for the "Marketing" department.
• When you enter this formula in a cell, Excel will calculate the average salary for employees in the "Marketing" department, considering only those rows where the department is "Marketing." In this example, the result is $(55000 + 58000 + 59000 + 57000) / 4 = 57250$.

Run this syntax/example in your spreadsheet for a better and clearer explanation.

AVERAGEIFS with Logical Operators:

Suppose you have a dataset of products, their sales, and the number of units sold. You want to calculate the average sales per unit for products that have sold more than 100 units. Here's a sample dataset:

You can use AVERAGEIFS with a logical operator (greater than) as follows:

• B2:B6 represents the range of sales (the average_range).
• C2:C6 represents the range of units sold (the criteria_range1).
• >100 is the criteria (the criteria1) for units sold greater than 100.

Run this syntax/example in your spreadsheet for a better and clear explanation.

AVERAGEIFS with Wildcard Characters:

Suppose you have a list of expenses, and you want to calculate the average of all expenses related to office supplies. However, the descriptions of these expenses may vary slightly. Here's a sample dataset:

You can use AVERAGEIFS with wildcard characters (the asterisk *) to match descriptions containing "Office Supplies" as follows:

• B2:B6 represents the range of amounts (the average_range).
• A2:A6 represents the range of descriptions (the criteria_range1).
• Office Supplies is the criteria (the criteria1) using asterisks as wildcards to match any description containing "Office Supplies."

Run this syntax/example in your spreadsheet for a better and clearer explanation.