How to Use Excel AVERAGEIFS Function with Multiple Criteria

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.


Download Practice Workbook

You can download practice workbook here.


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

2 Suitable Examples of Excel AVERAGEIFS Function with Multiple Criteria

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 Between Two Values in Excel


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.

Read More: AVERAGEIFS Function with “Not Equal to” Criteria (3 Examples)


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

Adnan Masruf

Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo