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.

## 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.

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

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: How to Use Excel AVERAGEIFS Function with Multiple Criteria**

### 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**.

**Download Practice Workbook**

Download the following workbook to practice by yourself.

## 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. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.