While working with data in Excel, we often need to do conditional averaging. Conditional averaging allows us to find the average of datasets that satisfy particular criteria. The AVERAGEIFS function allows us to average with multiple criteria. In this article, we will show how to use the Excel AVERAGEIFS function with multiple criteria.
Overview of Excel AVERAGEIFS Function
- Description
The AVERAGIFS function returns the average of values that satisfy multiple criteria. The criteria range and the criteria are to be stated explicitly in the arguments of the function.
- Generic Syntax
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
- Argument Description
| ARGUMENT | REQUIRMENT | EXPLANATION |
|---|---|---|
| average_range | Required | This is a cell or cells to average, with or without names, arrays, or references containing numeric values. |
| criteria_range1, criteria_range2, … | Required | Criteria_range1 is a must, the next criteria_ranges are optional. 1 to 127 ranges in which to evaluate the assigned criteria. |
| criteria1, criteria2, … | Optional | Criteria1 is a must , the next criteria are optional |
How to Use Excel AVERAGEIFS Function with Multiple Criteria: 2 Examples
In this article, we will show two examples of the AVERAGEIFS function. In the first example, we will use the Named Range feature to define the criteria ranges. Then, we will define the ranges without the Named Range.
1. With Named Ranges
You can refer to a set of cells by name rather than by range using Excel’s “Named Range” feature. It could be an entire column, row, or simply a few specific cells. Any operation on those cells can be performed after the designated range has been defined by invoking the name of the identified range. In this example, the use of the Named Range feature will allow us to use the function easily.
Steps:
- Firstly, select the G5 cell and write the following formula,
=AVERAGEIFS(Age,Gender,"Male", Department,"Sales")- Then, hit Enter.
- As a result, we will have the average of the values that satisfy the desired criteria.
Here, the “Age”, “Gender”, and “ Department” arguments in the AVERAGEIFS function represent the C5:C12, D5:D12, and E5:E12 ranges respectively.
Read More: Excel AVERAGEIFS with Multiple Criteria in Same Range
2. Without Named Ranges
In this instance, we will pass the arguments that require ranges without defining the ranges as named ranges. We will try to find out the average age of the employees who are male and are from the sales department.
Steps:
- To begin with, select the G5 cell and write the following formula,
=AVERAGEIFS(C5:C12,$D$5:$D$12,"Male", $E$5:$E$12,"Sales")- Then, hit the Enter button.
- Consequently, we will have the average of the cells that fulfill the conditions.
How to Use AVERAGEIFS Function Between Two Dates
In this example, we will use the AVERAGIFS function to get the average of the values that are between the two dates. Here, we will try to find out the average sales of a company between two dates.
Steps:
- To start with, select the E5 cell and write the following formula,
=AVERAGEIFS(C5:C10,$B$5:$B$10,"<=9/19/2022",$B$5:$B$10,">=9/15/2022")- Then, hit Enter.
- As a result, we will get the average sales between the two dates.
The greater than or equal to sign denotes “from” the date, and the less than or equal to sign means “to” the date in the formula.
Note:
- While writing the formula, check the date formatting of Excel in your device. Otherwise, the formula may think the format to be wrong and return no result. For example, if you write the date in “mm/dd/yyyy” format in the formula but the format in your device is “dd/mm/yyyy” then the formula will return no result.
Download Practice Workbook
You can download practice workbook here.
Conclusion
In this article, we have discussed two suitable examples to demonstrate the usage of the Excel AVERAGEIFS function with multiple criteria. This will allow users to average values with multiple conditions. This will reduce their efforts and save time.
Related Articles
- AVERAGEIFS Function with “Not Equal to” Criteria
- [Fixed!] How to Fix AVERAGEIFS Value (#VALUE!) Error in Excel
- Difference Between AVERAGEIF and AVERAGEIFS in Excel
- How to Use AVERAGEIFS Function for Multiple Columns
- AVERAGEIFS for Multiple Criteria in Different Columns in Excel
- How to Use AVERAGEIFS Between Two Values in Excel
