How to Use Excel AVERAGEIFS Function with Multiple Criteria

Get FREE Advanced Excel Exercises with Solutions!

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.

inserting formula to use excel averageifs function with multiple criteria

  • As a result, we will have the average of the values that satisfy the desired criteria.

using named range feature to use excel averageifs function with multiple 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.

typing formula to use excel averageifs function with multiple criteria

  • Consequently, we will have the average of the cells that fulfill the conditions.

Read More: How to Use AVERAGEIFS Function for Multiple Columns


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


<< Go Back to Excel AVERAGEIFS Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Adnan Masruf
Adnan Masruf

Adnan Masruf, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. His deep passion for research and innovation seamlessly aligns with his dedication to Excel. In this capacity, Masruf not only skillfully addresses challenging issues but also exhibits enthusiasm and expertise in gracefully navigating intricate situations, underscoring his steadfast commitment to consistently delivering exceptional content. His interests... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo