**Microsoft Excel **is a powerful software. We can perform numerous operations on our datasets using excel tools and features. There are many default **Excel Functions** that we can use to create formulas. Many educational institutions and business companies use excel files to store valuable data. Sometimes, we need to find the average value from a data range based on multiple conditions. There are multiple ways available to carry out the operation. This article will show you **4 **ideal examples of **AVERAGEIFS** **for** **Multiple Criteria **in **Different Columns **in **Excel**.

## Download Practice Workbook

Download the following workbook to practice by yourself.

## What Is AVERAGEIFS Function?

**The AVERAGEIFS function** is a mathematical and statistical function in excel that can calculate the average value from a range based on multiple criteria. The average is also called the arithmetic mean.

**Syntax**

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

**Arguments**

**average_range:** The range of data from where we’ll determine the average based on certain criteria. This is a mandatory argument.

**criteria_range1: **This mandatory argument is about the first criteria range.

**criteria1: **Our first condition. We’ll look for this condition in **criteria_range1**.

**[criteria_range2]: **This is the second criteria range. But it’s not mandatory. We’ll apply this argument when we need to find the average based on conditions more than one.

**[criteria2]: **Our second condition is when we’ll have multiple conditions.

Similarly, we can have as many criteria ranges and criteria as we need.

## 4 Ideal Examples of AVERAGEIFS for Multiple Criteria in Different Columns in Excel

**The AVERAGE** and **AVERAGEIF** functions can also calculate the average value. But they need other functions joined with them to satisfy multiple conditions in different columns. That makes the formula a complicated one. When we use **AVERAGEIFS**, we don’t have to rely on other functions anymore. So, that’s the biggest advantage of this function. Therefore, go through the examples to clearly understand the **AVERAGEIFS **and apply it to multiple criteria in different columns.

### 1. Find Average Sales from Multiple Criteria in Different Columns with AVERAGEIFS

In our first example, we’ll show how you can find the average sales amount of a certain product from a particular region using **AVERAGEIFS**. To illustrate, we’ll use a sample dataset as an example. For instance, the following dataset contains **Region**, **Product**, and **Sales**. Here, we’ll find the average sales of **AC **from the **West **region. So, learn the steps below to carry out the operation.

**STEPS:**

- First, select cell
**D16**. - Then, type the formula:

`=AVERAGEIFS(D5:D14,B5:B14,"West",C5:C14,"AC")`

**D5:D14**is the sales range,**B5:B14**is the region range from where we’ll look up for**West**and**C5:C14**is the second criteria range for**AC**.- Subsequently, press
**Enter**. - Thus, you’ll get the precise result.

**Read More:** **Difference Between AVERAGEIF and AVERAGEIFS in Excel**

### 2. Use AVERAGEIFS for Multiple Criteria in Different Columns Involving Date

Moreover, you can deal with the date through the **AVERAGEIFS **function. In the following dataset, we have the **Product**, **Sales**, and **Date **of those sales. And we want to figure out the average sales amount of **Cable **in the month of **January**. Therefore, follow the step below to perform the task.

**STEPS:**

- Firstly, click cell
**F4**. - There, input
**1/1/2022**to denote the first day of**January**. - Now, select the cell and press
**Ctrl**and**1**keys together. - As a result, the
**Format Cells**dialog box will pop out. - Next, under the
**Number**tab, choose**Custom > mmmm**to display the month name automatically. - Afterward, press
**OK**.

- Subsequently, select
**G4**. - Type the formula:

`=AVERAGEIFS(D5:D14,B5:B14,">="&F4,B5:B14,"<="&EOMONTH(F4,0),C5:C14,"Cable")`

**The EOMONTH function**gives out on the last day of**January**.- Here, we have applied three criteria ranges along with the criteria.
**D5:D14**is the average range.**B5:B14**is the second and third criteria to find for the dates in**January**Then, we’ll find the**Cable**in**C5:C14**.- Click
**Enter**. - In this way, you’ll get the desired average.

**Read More: ****How to Apply AVERAGEIFS Function Between Two Dates in Excel**

### 3. Get Average Age from Multiple Criteria in Different Columns Using AVERAGEIFS

The following dataset contains the **Weight **in **kg**, **Height **in **cm**, and **Age **of some teenagers. Here, we want to determine the average age of teenagers who are above **55 kg **and below **175 cm**. Hence, learn the process to carry out the operation.

**STEPS:**

- First of all, choose cell
**D16**. - Insert the formula:

`=AVERAGEIFS(D5:D14,B5:B14,">55",C5:C14,"<175")`

- Hit
**Enter**. - Consequently, the average age will appear.

**Read More:** **How to Use AVERAGEIFS Function for Multiple Columns**

### 4. Insert AVERAGEIFS Function to Compute Average Marks in Excel

Lastly, we’ll seek the average marks of students in section B in Math. By this time, you’ve become quite familiar with the **AVERAGEIFS **function. So, this won’t be a tough task for you. See the process below.

**STEPS:**

- Select cell
**D16**at first. - Then, type the formula:

`=AVERAGEIFS(D5:D14,B5:B14,"Math",C5:C14,"B")`

- After that, press
**Enter**. - Hence, the average marks of the desired students will emerge.

**Read More:** **Excel AVERAGEIFS with Multiple Criteria in Same Range**

## Things to Remember

- This function has a limitation. We can easily do logical
**AND**operation but we can’t carry out the**OR**That is, all the conditions need to be satisfied with this function. - If the
**average_range**is blank or text values that excel can’t translate into numbers, and if all the conditions are not met, you’ll get the**#DIV0!**error. - The criteria ranges and the average range must be of the same size & shape.
- We can use wildcard characters in the function argument.
- If any cell is empty in the criteria range, this function considers it as
**0**.

## Conclusion

Henceforth, you will be able to use **AVERAGEIFS **for **Multiple Criteria **in **Different Columns** in **Excel **using the above-described examples. Keep using them and let us know if you have more ways to do the task. Follow **the ExcelDemy** website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.