We often need to determine the sum and average of some numbers in Excel. There are many ways to calculate these. In this article, we will learn some handy approaches to calculate sum and average with the formula in Excel. Letâ€™s see the approaches below.

## How to Calculate Sum & Average with Excel Formula: 3 Handy Approaches

This tutorial will guide you with 3 handy approaches to calculate sum and average using the built-in Excel formula. For this, we will use the dataset of range **B4:F11** that contains the names of some students and their marks in *Math, Physics, *and *Chemistry*. First, we will calculate the total marks in all the subjects for each student in the range **F5:F10** and then we will calculate the average of the total marks in cell **F11**.

### 1. Insert SUM & AVERAGE Formulas with AutoSum Tool in Excel

In the first approach, we will use the **AutoSum** tool. The **AutoSum** tool performs calculations quickly. This tool automatically selects the range to evaluate. However, we can also select the range manually. The steps to use this tool for inserting the** SUM** and **AVERAGE** functions are below.

**Steps:**

- First of all, we need to calculate the
**Total**marks of the first student (*John*) in the three subjects. - Activate cell
**F5**andÂ go to the**Home**tab.

- Click on the
**AutoSum**drop-down in the**Editing**group.

- To calculate the
**Total**marks, select**Sum**from the drop-down menu.

- As a result, the
**SUM**function with the range**C5:E5**will be inserted automatically in the selected cell (**F5**). - Consequently, we will see the following formula in cell
**F5**.

`=SUM(C5:E5)`

- Now, press the
**Enter**key andÂ you will get the**Total**marks of*John*in cell**F5**.

- To get the
**Total**marks of the other students, put your cursor in the bottom-right corner of cell**F5**. - Thus, you will see a
*plus*sign (**+**) which is called the**Fill Handle**.

- After that, drag the
**Fill Handle**to autofill the rest of the cells in the range**F6:F10**. - See the picture below.
- In this way, we can sum up the marks of the students.

- Subsequently, we will calculate the
**Average**marks of all the students. - For this reason, select cell
**F11**> go to the**Home**tab.

- Next, click on
**Average**from the**AutoSum**dropdown menu.

- Accordingly, you will get the formula below in cell
**F11**.

`=AVERAGE(F5:F10)`

- Finally, press the
**Enter**key. - See the average value in cell
**F11**of the screenshot below.

### 2. Calculate Sum & Average in Excel with SUM & AVERAGE Functions

In this approach, we will use the built-in Excel functions to determine the sum and average. Here, we will use the same dataset as the previous method.

#### 2.1 Inserting Range Reference

In this method, we will enter the range reference in the **SUM** and the **AVERAGE **functions to calculate the sum and average.

**Steps:**

- Firstly, go to cell
**F5**and type`"`

`=`

`"`

.

- Then, to calculate the total marks of
*John*type**SUM**and you will see the option**SUM**Â below the cell.

- Now, double-click on the
**SUM**option. - Thus, the
**SUM**function will be inserted in the cell.

- Afterward, select the range
**C5:E5**.

- Next, close the parenthesis.
- The final formula will be:

`=SUM(C5:E5)`

- Therefore, press the
**Enter**key. - In this way, you can calculate the total marks of
*John*.

- Eventually, drag the
**Fill Handle**to**Autofill**the cells for the rest of the students (**F6:F10**).

- Similarly, we can determine the average marks of all the students in the class.
- In the first place, go to cell
**F11**> type`"`

`=`

`"`

.

- Thereupon, type
**AVERAGE**Â and double-click on the**AVERAGE**option.

- At this time, select the range
**F5:F10**.

- Later, close the bracket.
- The final look of the formula will be:

`=AVERAGE(F5:F10)`

- Press the
**Enter**key and find the output.

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

#### 2.2 Enter Numbers Directly

In this method, we will enter the numbers manually in the **SUM** and the **AVERAGE** functions to calculate the sum and average of those numbers.

**Steps:**

- First, select cell
**F5**. - Next, to get the total mark of
*John*, type the following formula.

`=SUM(94,82,78)`

In this formula, we typed marks of *John *in *Math, Physics,* and *Chemistry *manually.

- After pressing the
**Enter**key, we will get the total mark of*John*. - Eventually, drag the plus sign to
**Autofill**the range**F6:F10**.

- To calculate the average mark enter the formula below in cell
**F11**.

`=AVERAGE(254,245,251,248,240,246)`

- Lastly, press the
**Enter**key.

**Read More: **How to Average Negative and Positive Numbers in Excel

#### 2.3 With Mixed Arguments

Here, we will calculate the sum and average inserting mixed arguments. Here, we will enter both the cell references and the direct numbers in the formula.

**Steps:**

- First, activate cell
**F5**. - Next, type the following formula to get the total marks of the first student in all subjects.

`=SUM(C5,D5,78)`

In the formula, **C5** & **D5** are the cell references whereas 78 is a number.

- After pressing the
**Enter**key, find the result in cell**F5**.

- Finally, drag the plus sign to
**Autofill**the rest of the cells of range**F6:F10**.

- To get the average marks of the students, type the following formula in cell
**F11**.

`=AVERAGE(F5,F6,251,F8,F9,F10)`

- Finally, press the
**Enter**key and get the result.

In this formula, **F5**, **F6**, **F8**, **F9** and **F10** are cell references. Besides, 251 is a number. So, we can see that the **SUM** and the **AVERAGE** functions support mixed arguments.

**Read More:** How to Average Filtered Data in ExcelÂ

#### 2.4 For Blank Cells and Zero Values

In this method, we will see the calculation of the sum and average with blank cells and zero values. First of all, we will see the calculation with blank cells. For this, we will use the dataset (**B4:F11**) below. We can see that cells **D5**, **E7**, **C9**, and **E10** are blank cells. Now, we will insert these cells along with the cells with numbers to calculate the total and average marks of the students.

**Steps:**

- In the beginning, select cell
**F5**. - Then, to calculate the total marks of
*John*, type the formula below.

`=SUM(C5:E5)`

- Finally, press the
**Enter**key to get the result. - We can see that the
**SUM**formula ignores the blank cell (**D5**) and adds the rest of the cells (**C5**,**E5**).

- Similarly, we can calculate the total marks for other students by dragging the
**Fill Handle**.

- At this time, we will calculate the average marks of the students of range
**F5:F10**. - To get the average marks type the formula below in cell
**F11**.

`=AVERAGE(F5:F10)`

The cell **F10** in the formula is a blank cell.

- After pressing the
**Enter**key, we get the average mark in cell**F11**. - We can see that the
**AVERAGE**formula totally ignored the blank cell (**F10**) and calculated the average for the cells containing numbers (**F5:F9**).

- On the other hand, if cell
**F10**contains a zero value (**0**) then we get the result like the picture below. - So, the
**AVERAGE**formula included the cell (**F10**) with zero value in the calculation.

**Read More: **How to Calculate Average in Excel Excluding 0

#### 2.5 SUM Function with AVERAGEA Function

Suppose, we have a dataset (**B4:F11)** below. But this time, the dataset contains a text string (*Absent*) in the *Total* column. We need to calculate the sum and average of the numbers. Here, we will see how we can include the text string to calculate the average. To do so, we will use **the AVERAGEA function** in Excel that includes both the text strings and numbers in the calculation.

**Steps:**

- To begin, calculate the total marks using the
**SUM**function like the previous one.

- Now, if we use the
**AVERAGE**function to calculate the average marks, we will see that this function does not consider the text string in the calculation. - However, it ignores the text string and calculates the average for the cells containing numbers.

- To include the text string in the calculation of the average, we can use the
**AVERAGEA**function. - In that case, type the following formula in cell
**F11**:

`=AVERAGEA(F5:F10)`

- Press
**Enter**. - Finally, see the result in cell
**F11**of the picture below which shows that the function included the cell (**F8**) containing text string.

**Read More:** How to Do Subtotal Average in ExcelÂ

#### 2.6 Combine SUM & AVERAGEIF Functions

Here, we will use the **SUM** function to calculate the total marks and then **the AVERAGEIF function** to calculate the average marks. The **AVERAGEIF** function calculates the average of some numbers with specific criteria.

**Steps:**

- In the first place, calculate the total marks (
**F5:F10**) using the**SUM**function (described in the previous methods). - Enter the formula below in cell
**F11**to calculate the average of the total marks that are greater than 245.

`=AVERAGEIF(F5:F10,">245")`

- After entering the formula, press the
**Enter**key and get the result in cell**F11**of the image below.

#### 2.7 SUM Function with AVERAGEIFS Function

In this method, we will first calculate the total marks with the **SUM** function. Then, we will use **the AVERAGEIFS function** to determine the average. The **AVERAGEIFS** function calculates the average of some numbers with multiple criteria.

**Steps:**

- First, use the
**SUM**function to calculate the total marks (**F5:F10**) like the previous methods. - Calculate the average marks of the students by inserting the following formula in cell
**F11**.

`=AVERAGEIFS(F5:F10,B5:B10,"*e*",E5:E10,">70")`

This formula calculates the average of the total marks (**F5:F10**) for the students whose names (**B5:B10**) contain the letter `"`

`e`

`"`

Â and whose marks in *Chemistry *(**E5:E10**) are greater than 70. In this formula, we used the wildcard (*****) to denote the letter `"`

`e`

`"`

.

### 3. Use Array Formula to Find Sum & Average in Excel

In the last method, we will first calculate the sum following any of the previous methods and then the average using the array formula. This array formula contains the **LARGE** and **AVERAGE** functions.

**Steps:**

- Firstly, calculate the total marks (
**F5:F10**) using the**SUM**function like before.

- Afterward, go to cell
**F11**. - Then, to determine the average of the three largest marks (
*Total*), enter the following formula in cell**F11**.

`=AVERAGE(LARGE(F5:F10,{1,2,3}))`

- Press
**Ctrl**+**Shift**+**Enter**to terminate the array formula. - Finally, you will get the desired average in cell
**F11**like the picture below.

ðŸ”Ž **How Does the Formula Work?**

**LARGE(F5:F10,{1,2,3}):**Returns the three largest numbers from the**F5:F10**range.**AVERAGE(LARGE(F5:F10,{1,2,3})):**Returns the average of the three largest numbers.

**Read More: **How to Calculate Average of Top 5 Values in Excel

**Download Practice Workbook**

Download the practice workbook from here.

## Conclusion

I hope the above tutorial will be helpful for you to calculate the sum and average with the formula in Excel. Download the practice workbook and give it a try. Let us know your feedback in the comment section.