While working in Excel, we often need to use the **AVERAGEIFS function** **for multiple columns**. Calculating the average value for multiple columns when conditions are applied can be a difficult task for a large dataset. But Excel has some powerful functions, like the **AVERAGEIFS **function, to handle such types of situations quite efficiently. Let’s explore **2 **examples of the **AVERAGEIFS function for multiple columns**. Later, you’ll see the ways to find the average from multiple columns dealing with single and multiple conditions.

## Download Practice Workbook

## 2 Suitable Examples to Use AVERAGEIFS Function for Multiple Columns

In this section of the article, we will discuss **2 **suitable examples of the **AVERAGEIFS function for multiple columns**. Not to mention that we have used the *Microsoft Excel 365 *version for this article, you can use any other version according to your convenience.

### Example 01: Calculating Average Price

In our first example, we’ll use the **AVERAGEIFS **function to determine the **Average Price**. Consider that our dataset is a **Price Chart of ABC Shoes**. Our goal is to determine the **Average Price** based on many factors. Here, we’ll apply **2 **criteria (**Size, Color**). Let’s follow the steps mentioned below to do this.

__Steps:__

- Firstly, create a table as marked in the following image in your worksheet.

- Following that, enter the formula given below in cell
**D22**.

`=AVERAGEIFS($D$5:$D$19,$B$5:$B$19,B22,$C$5:$C$19,C22)`

Here, the range **$D$5:$D$19** refers to the cells of the **Price **column, the range **$B$5:$B$19** represents the cells of the **Size **column, the range **$C$5:$C$19** indicates the cells of the **Color **column, the cell **B22 **refers to the cell of the **Criteria-1** column, and the cell **C22 **indicates the cells of the **Criteria-2** column.

- Then, press
**ENTER**.

Consequently, you will have the **Average Price** for **Size M **shoes in **Blue Color**.

- Now, use the
**AutoFill**feature of Excel to get the rest of the outputs as demonstrated in the following picture.

**Read More: ****How to Use Excel AVERAGEIFS Function with Multiple Criteria**

### Example 02: Computing Average Marks

Now, we will apply the** AVERAGEIFS** function to determine the **Average Marks**. For instance, we have the **Marks of Grade 6 and Grade 7 Students** as our dataset. Our goal is to calculate the **Average Marks** of the students based on **2 ** criteria (**Grade, Section**). Let’s follow the procedure discussed in the following section.

__Steps:__

- Firstly, create a table like shown in the following image.

- Now, enter the following formula in cell
**D19**.

`=AVERAGEIFS($D$5:$D$16,$B$5:$B$16,B19,$C$5:$C$16,C19)`

Here, the range **$D$5:$D$16** refers to the cells of the **Marks **column, the range**,$B$5:$B$16** represents the cells of the **Grade **column, the range **$C$5:$C$16** indicates the cells of the **Section **column, the cell **B19 **refers to the cell of the **Criteria-1** column and the cell **C19 **indicates the cells of the **Criteria-2** column.

- After that, hit
**ENTER**.

As a result, you will have the **Average Marks** of the students of **Section B** of **Grade 6**.

- Finally, by using the
**AutoFill**option of Excel, you can get the remaining outputs.

However, the **AVERAGEIFS **function **can also be employed in a wide range of situations**.

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

## How to Calculate Average of Multiple Columns Based on Single Condition

While working in Excel, sometimes we need to calculate the **average of multiple columns** based on a** single condition**. In the previous methods, we used the **AVERAGEIFS **function. But unfortunately, the **AVERAGEIFS **function doesn’t accept multiple columns as **average_range **input. In this section of the article, we will use **2 **approaches to calculate the average of multiple columns based on a single condition.

For instance, we have an **Experimental Data Analysis** as our dataset. In the dataset, we have **3 Experiment Codes**. Each Experiment has **4 **sets of data for **5 Iterations**. Our goal is to calculate the **Average **of each **Experiment **data for all **Iterations**.

### 1. Applying AVERAGE and FILTER Functions

Applying **AVERAGE** and **FILTER** functions is one of the smartest ways to calculate the average of multiple columns based on a single condition. The **AVERAGE **function simply returns an average value of the cells of a selected range. The **FILTER **function can filter out a range of an array based on a condition. Let’s follow the steps mentioned below to do this.

__Steps:__

- Firstly, create a table as shown in the following image.

- After that, enter the formula given below in cell
**C19**.

`=AVERAGE(FILTER($C$5:$G$16,$B$5:$B$16=B19))`

Here, the range **$C$5:$G$16** refers to the values of the **Experiment **data in** 5 Iterations**, the range **$B$5:$B$16 **indicates the cells of the **Experiment Code **column, and the cell **B19 **refers to the cell of the **Experiment **column.

**Formula Breakdown**

**FILTER($C$5:$G$16,$B$5:$B$16=B19) →**It returns the filtered data from the range**$C$5:$G$16**based on a condition.**$C$5:$G$16 →**It is theargument.*array***$B$5:$B$16=B19 →**This refers to theargument.*include***Output →****{30,5,29,2,16;11,13,13,30,11;10,12,22,12,20;26,21,14,22,9}**.

**AVERAGE(FILTER($C$5:$G$16,$B$5:$B$16=B19))****→**It becomes**AVERAGE({30,5,29,2,16;11,13,13,30,11;10,12,22,12,20;26,21,14,22,9})**.**Output → 16.40.**

- Then press
**ENTER**.

As a result, you will have the **Average **of all the **Iterations **of **Experiment A**.

- Finally, use the
**AutoFill**option to get the remaining outputs as demonstrated in the following picture.

**Read More:** **How to Use AVERAGEIFS Between Two Values in Excel**

### 2. Using AVERAGE and IF Functions

Using the **AVERAGE **and **IF** functions is another efficient way to calculate the average of multiple columns based on a single condition. Let’s use the procedure discussed in the following section to do this.

__Steps:__

- Firstly, create a table as demonstrated in the image below.

- After that, enter the following formula in cell
**C19**.

`=AVERAGE(IF($B$5:$B$16=$B19,$C$5:$G$16))`

Now, the **AVERAGE **function will return the average of the cells that satisfies the **IF **condition.

- Then press
**ENTER**.

Consequently, you will have the **Average **value of **Experiment A **for all **Iterations**.

- Now, use the
**AutoFill**option of Excel to have the rest of the outputs as shown in the following image.

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

## How to Calculate Average of Multiple Columns Based on Multiple Conditions

In Excel, we often need to calculate the average of multiple columns based on multiple conditions. Using the **Power Query** feature of Excel can be an efficient way to do this. Let’s use the procedure discussed in the following section.

__Steps:__

- Firstly, select your entire dataset and press the keyboard shortcut
**CTRL + T**. - Now, check the box of
**My table has headers**in the**Create Table**dialogue box. - Then, click on
**OK**.

As a result, you will have the following output on your worksheet.

- Following that, select your table and go to the
**Data**tab from**Ribbon**. - Then, choose the
**From Table/Range**option from the**Get & Transform Data**group.

Subsequently, the **Power Query Editor** window will open as shown in the image below.

- Now, right-click on the
**Experiment**column. - After that, choose the
**Unpivot Other Columns**option.

As a result, your dataset will look like the following picture.

- Next, click on the
**Close & Load**option.

Consequently, you will be redirected to your workbook with your modified dataset.

- After that, create a table as shown in the image below.

- Then, enter the following formula in cell
**H5**.

`=AVERAGEIFS(Table1__3[Value],Table1__3[Experiment Code],F5,Table1__3[Iteration],G5)`

Here, **Table1__3[Value]** refers to the cells of the **Value **column, **Table1__3[Experiment Code]** indicates the cell of the **Experiment Code **column, **Table1__3[Iteration]** represents the cells of the **Iteration **column, the cell **F5 **refers to the cell of the **Experiment** column, and the cell **G5 **indicates the cell of the **Iteration **column of the output table.

Now, the **AVERAGEIFS **function will return the average of the cells of the **Value **column based on the specified conditions.

- Now, press
**ENTER**.

Consequently, you will have the **Average** of **Experiment A **for **Iteration-1**.

- Now, use the
**AutoFill**feature to get the rest of the outputs as demonstrated in the following image.

## Practice Section

In the **Excel Workbook**, we have provided a **Practice Section **on the right side of the worksheet. Please practice it by yourself.

## Conclusion

That’s all about today’s session. I strongly believe that this article was able to guide you to **use AVERAGEIFS function for multiple columns**. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. To learn more about Excel, you can visit our website, **ExcelDemy**. Happy learning!