How to Calculate Average from Different Sheets in Excel

Average is a very useful mathematical operation. In order to find the average of a few numbers, we need to sum the input numbers and divide them by the number of inputs. We can easily calculate the average of input numbers in Excel using various methods. We can even calculate the average of inputs from different sheets of a workbook. In this article we will show the methods to calculate average from different sheets in Excel.


Download Practice Workbook

You can download the practice workbook from here.


3 Methods to Calculate Average from Different Sheets in Excel

We can calculate the average from different sheets in Excel. In this article, we will discuss 3 such simple methods to calculate the average from different sheets. Suppose we have 3 sheets containing data on sales of different shoes. We want to calculate the average. The sheets for our inputs are given below.

Sheet Jan (January)

how to calculate average from different sheets in Excel

Sheet Feb (February)

Sheet Mar (March)

dataset for average calculation from different sheets


Method 1: Calculating Average of Same Range of Cells from Different Sheets

In this method, we will use the AVERAGE function of Excel to calculate average from different sheets. We are showing the steps below.


1.1. Average of a Single Cell from Different Sheets

Here we will show the steps to average a single cell from each sheet.

Step 1: At first we have to select the cell where we want to put the average of inputs from different sheets. In our case, the cell is C6 of sheet Method 1 where we will calculate the average of C6 cells of sheets Jan, sheet Feb, and sheet Mar.

average from different sheets method1

Step 2: We have to enter the cell and write the following formula in the cell.

=AVERAGE(Jan:Mar!C6)

Here, Jan:Mar! Is the sheet range and C6 is the cells for input.

Step 3: We need to press the ENTER key and use Fill Handle to copy the formula to the cells below for other averages.

average of different sheets method 1.1 result

We can see the results in the cells.


1.2. Average of Multiple Cells from Different Sheets

Here we will show the steps to average multiple cells from multiple sheets

Step 1: We have to select the cell where we want to see the average. In our case the cell is C11.

calculate grand average from different sheets in excel

Step 2: We have to enter the cell and put the following formula into the cell.

=AVERAGE(Jan:Mar!C6:C9)

calculate average from different sheet in excel formula input

Here, Jan:Mar! Is the range of sheets and C6:C9 is the range of cells from the three input sheets.

Step 3: We need to press the ENTER key.

grand average result different sheets

We can see the result in the cell which is the average from the selected range of multiple selected sheets.


Method 2: Calculating Average of Different Ranges of Cells from Different Sheets

Sometimes we may need to find the average of cells from different ranges of different sheets. In that case, we can’t use the sheet range in the AVERAGE function. We will show the steps to follow for doing that.

Step 1: We have to select the cell where we want to get the average.

calculate average from different sheets in excel method 2

Step 2: We have to write the following formula in the cell.

=AVERAGE(Jan!C6:C7,Feb!C8:C9,Mar!C7:C8)

Here, Jan!C6:C7 is the sheet with a specific range for input. The same thing goes with the other two parts of the formula.

Step 3: We need to press the ENTER key.

average from different sheets method 2 result

We can see the average of different ranges from different sheets here.


Method 3: SUM and COUNT Functions to Calculate Average from Different Sheets

Instead of using the AVERAGE function, we can also combine the SUM and COUNT functions to do the same task. We will show two different types of averages here.


3.1. Average of a Single Cell from Different Sheets

Firstly we will show the steps to calculate the average of a single selected cell from different sheets.

Step 1: We have to select the cell where we want to see the result. In our case the cell is C6.

average from different sheets in excel using SUM COUNT function

Step 2: We have to enter the cell and write the following formula in it.

=SUM(Jan:Mar!C6)/COUNT(Jan:Mar!C6)

Here, Jan:Mar!C6 is the sheet range with a specific cell. We use the mathematical operator division(\) in the formula.

Step 3: We need to press the ENTER key and use AutoFill to copy the formula to the cells below.

average from different sheets using SUM COUNT function result

We can see the average in each cell.


3.2. Average of Multiple Cells from Different Sheets

We can also calculate the average of multiple cells from different sheets using the SUM and COUNT functions. We will show the steps to do that below.

Step 1: We have to select the cell where we want the result.

calculate average from different sheets in excel sum count function use

Step 2: We need to enter the cell and write the following formula there.

=SUM(Jan:Mar!C6:C9)/COUNT(Jan:Mar!C6:C9)

Here, Jan:Mar!C6:C9 is the sheet range with a specific cell range for the inputs.

Step 3: We need to press the ENTER key.

average from different sheets using SUM COUNT function result

We can see the average in the cell.


Conclusion

Average is a very basic mathematical operation which helps with data analysis. In Excel, we can calculate the average from a cell or multiple cells of different sheets. We can choose the input range as we need. In this article, we have shown 3 such simple methods to calculate average from different sheets. Hope it will be helpful to you. If you have faced any problems with the methods or if you have any suggestions, please let us know by comment.

Mehedi Hasan Shimul

Mehedi Hasan Shimul

Hi! I am Mehedi Hasan Shimul. As I am an Engineer solving different problems with the help of Excel amuses me. I write Excel related different problem solving articles here. Hope it will help you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo