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.


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.

averageifs multiple columns

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.

Using Excel formula to use the AVERAGEIFS function for multiple columns

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.

Final output of method 1 to use the AVERAGEIFS function for multiple columns

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.

Computing Average Marks by using AVERAGEIFS for multiple column

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.

Final output of method 2 by using AVERAGEIFS for multiple column in Excel

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.

How to Calculate Average of Multiple Columns Based on Single Condition


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.

Applying AVERAGE and FILTER Functions in Excel

  • 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 the array argument.
    • $B$5:$B$16=B19 → This refers to the include argument.
    • 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.

Final output of method 3 to use the AVERAGEIFS function for multiple columns

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.

Using AVERAGE and IF Functions to use the AVERAGEIFS function for multiple columns

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

Final output of method 4 to use the AVERAGEIFS function for multiple columns

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.

How to Calculate Average of Multiple Columns Based on Multiple Conditions in Excel

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.

Final output of method 5 to use the AVERAGEIFS function for multiple columns


Practice Section

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

Practice section to use the AVERAGEIFS function for multiple columns


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!


Related Articles

Zahid

Zahid

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo