In this tutorial, I will show some methods for calculating the average of multiple columns in Excel. Usually, you can get an average of a range of having multiple columns using the **AVERAGE** function. However, if you want to have an average from a certain number of columns, there are ways available in Excel. I will use several Excel functions and their combinations to get the average from multiple columns.

## How to Calculate Average of Multiple Columns in Excel: 6 Methods

### 1. Calculate Average of Multiple Columns Using AVERAGE Function

Firstly, I will use **the AVERAGE function **to calculate the average of some adjacent ranges located in different columns. For example, I have a dataset (**B4:E11**) with several studentsâ€™ test scores. Now, I will calculate the average of multiple ranges from multiple columns.

**Steps:**

- Type the below formula in cell
**B13**to calculate the average of ranges**B5:B10**,**C5:D9**, and**E6:E11**.

`=AVERAGE(B5:B10,C5:D9,E6:E11)`

- Hit
**Enter**and you will get the average of the specified ranges of columns**B**,**C**,**D**, and**E**.

**Read More: **How to Average a Column in Excel

### 2. Define a Name to Multiple Columns and Then Get the Average

Sometimes, selecting multiple ranges from multiple columns may seem tiresome and may result in wrong results if the ranges are not selected correctly. Luckily, you can name the specified range in Excel and then pass the range as the argument of the **AVERAGE** function.

**Steps:**

- First, select the expected ranges from multiple columns by pressing the
**Ctrl**key. - Then go to the
**Name Box**, give a name, and press**Enter**. I have named the below ranges as*MultiCol*.

- Now type the below formula in cell
**B13**and hit**Enter**.

`=AVERAGE(MultiCol)`

- Here is the ultimate average you will get.

**Read More:** How to Calculate Average of Multiple Ranges in ExcelÂ

### 3. Excel AVERAGEIF Function to Calculate Average of Multiple Columns

Now, I will use **the AVERAGEIF function** to get the average of multiple columns. In the following discussion, I will show you two examples of this function to calculate the average.

#### 3.1. Get Average of Cells that Match a Criteria Exactly

Suppose, I have a dataset (**B4:C12**) containing several fruit names and their qualities in columns **B **and **C**. I will look for particular fruit names (here, *Apple)* in column **B** and calculate their average from column **C**.

**Steps:**

- Type the following formula in cell
**C14**and hit**Enter**.

`=AVERAGEIF(B5:B12,"Apple",C5:C12)`

- I will get the average of the quantities of all apples on this dataset.

#### 3.2. Calculate Average of Cells that Match Criteria in a String

Previously, I calculated the average for a fruit name that was an exact match. Now, I will search for a string that matches the cell contents and then calculate the average from another column. For instance, I have a fruit name containing the string *Apple*Â as part of the cell content (e.g. *Wood Apple, Pineapple*, etc.). Letâ€™s find a match for the string *Apple*Â in column **B** and then get the corresponding average from column **C**.

**Steps:**

- Type the below formula in cell
**C14**.

`=AVERAGEIF(B5:B12,"*Apple*",C5:C12)`

- Press
**Enter**and get the below result.

### 4. Combination of AVERAGEIF and SUMIF Functions to Get an Average of Multiple Columns

You can use a combination of Excel functions such as the **AVERAGEIF** and **SUMIF** functions to find the average from multiple columns. For example, I have a dataset (**B4:E10**) containing some grocery items and their unit prices and sold quantities by dates. Now, I will calculate the total price for these items from columns** B**, **C**, and** E** using the **SUMIF** and **AVERAGEIF **functions.

**Steps:**

- Type the below formula in cell
**Â E13**and hit**Enter**.

`=AVERAGEIF($B$5:$B$10,B13,$E$5:$E$10)*SUMIF($B$5:$B$10,B13,$C$5:$C$10)`

- We will get the below result. Use the
**Fill Handle**(**+**) tool to copy the formula to the rest of the cells.

- Finally, we will get the total price for all the items as below.

🔎** How Does the Formula Work?**

**âž¤**** AVERAGEIF($B$5:$B$10,B13,$E$5:$E$10)
**This part of the formula returns the

*Unit Price*of the cell content of cell

**B13**(

*Corn Flakes*) which is: {

**5**}

**âž¤**** SUMIF($B$5:$B$10,B13,$C$5:$C$10)
**Now, this part of the formula returns the sold

*Quantity*of

*Corn FlakesÂ*which is: {

**88**}

**âž¤**** AVERAGEIF($B$5:$B$10,B13,$E$5:$E$10)*SUMIF($B$5:$B$10,B13,$C$5:$C$10)
**Finally, the above formula multiplies

**5**by

**88**and returns: {

**440**}

### 5. Combination of Excel AVERAGE and LARGE Functions to Get Average from Multiple Columns

You can combine **the LARGE function** along with the **AVERAGE **function to find the average of a range spread in multiple columns. Such as, I will apply this combination of Excel functions to calculate the average of the top 3 values of the range **B11:E11**.

**Steps:**

- Type the below formula in cell
**B13**and press**Enter**.

`=AVERAGE(LARGE(B11:E11, {1,2,3}))`

- As a result, I will get the average of the top 3 values from the range
**B11:E11**which is spread over multiple columns.

Here, the** LARGE** function returns the 3 largest values (89, 87, & 77) in the range **B11:E11**. Later, the **AVERAGE** function returns the average of the above 3 numbers.

**⏩**** Note:
**You can use

**the SMALL function**along with the

**AVERAGE**function to calculate the average of the smallest numbers in a range spread over multiple columns.

### 6. Excel OFFSET, AVERAGE, and COUNT Functions to Calculate the Average of Last N Values in Multiple Columns

I will use **the OFFSET function **along with the **COUNT** and **AVERAGE** functions to calculate the average of the last **N** values which are spread over several columns. Such as I will calculate the average of the last three (3) values of the range **B5:F5 **of my dataset (**B4:F11**).

**Steps:**

- Type the following formula in cell
**B13**and hit**Enter**.

`=AVERAGE(OFFSET(B5,0,COUNT(B5:F5)-3,1,3))`

- Consequently, you will get below average.

🔎** How Does the Formula Work?**

**âž¤**** COUNT(B5:F5)
**This part of the formula returns: {

**5**}

**âž¤**** (OFFSET(B5,0,COUNT(B5:F5)-3,1,3)
**Now, this part of the formula returns the last 3 values of the range

**B5:F5**: {

**99**,

**77**,

**66**}

**âž¤**** AVERAGE(OFFSET(B5,0,COUNT(B5:F5)-3,1,3))
**Finally, the formula returns the average of the last 3 values (99,77,66) which is: {

**80.66666667**}

**Read More: **How to Average Every Nth Row in Excel

**Download Practice Workbook**

You can download the practice workbook that we have used to prepare this article.

## Conclusion

In the above article, I have tried to discuss several methods to calculate the average of multiple columns in Excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

**Related Articles**

- How to Calculate Average Only for Cells with Values in Excel
- How to Exclude a Cell in Excel AVERAGE Formula
- How to Find Average of Specific Cells in Excel
- How to Average Only Visible Cells in Excel
- How to Find Average with Blank Cells in Excel
- How to Fix Divide by Zero Error for Average Calculation in Excel
- How to Ignore #N/A Error When Getting Average in Excel
- [Fixed!] AVERAGE Formula Not Working in Excel

**<< Go Back to Calculate Average in Excel | How to Calculate in ExcelÂ | Learn Excel**