How to Use AVERAGEIFS Function for Multiple Columns

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.

How to Use AVERAGEIFS Function for Multiple Columns: 2 Suitable Examples

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.

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.

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.

Read More: AVERAGEIFS for Multiple Criteria in Different Columns in Excel

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.

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.

• 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: Excel AVERAGEIFS with Multiple Criteria in Same Range

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.

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.

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.

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.

Download Practice Workbook

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. Happy learning!

Related Articles

• [Fixed!] How to Fix AVERAGEIFS Value (#VALUE!) Error in Excel
• Difference Between AVERAGEIF and AVERAGEIFS in Excel
• How to Apply AVERAGEIFS Function Between Two Dates in Excel
• How to Use AVERAGEIFS Between Two Values in Excel
• AVERAGEIFS Function with “Not Equal to” Criteria

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