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.

## Download Practice Workbook

Download the practice workbook from here.

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

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 (**B4:F11**) below 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**. So, without further delay, let’s get started.

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

In the first approach, we will use the **AutoSum** tool in Excel for inserting the formula to calculate the **sum** and **average**. We can use the **AutoSum** tool for doing the excel 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 function** and **the Average function** are below.

**Steps:**

- First of all, we need to calculate the
**Total**marks of the first student (**John**) in the**three**subjects. - To do so, activate cell
**F5**first. - Secondly, go to the
**Home**tab.

- Thirdly, click on the
**AutoSum****drop-down**in the**Editing**group.

- Fourthly, 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. - Hence, 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 (**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.

**Read More:** **How to Calculate Average True Range in Excel (with Easy Steps)**

**Similar Readings**

**How to Average Filtered Data in Excel (2 Easy Methods)****Calculate Moving Average for Dynamic Range in Excel (3 Examples)****How to Fix Divide by Zero Error for Average Calculation in Excel****Calculate Average from Different Sheets in Excel****How to Calculate Monthly Average from Daily Data in Excel**

### 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. Let’s discuss the methods below.

#### 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**. The steps are below.

**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 (see screenshot).

- 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)`

- Ultimately, press the
**Enter**key and find the output (see screenshot).

**Read More:** **How to Calculate Average of Multiple Ranges in Excel (3 Methods)**

#### 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. Let’s see the steps below to do so.

**Steps:**

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

`=SUM(94,82,78)`

In this formula, we typed the 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. - See the result in the following picture.

**Read More:** **[Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)**

#### 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. The steps to do so are below.

**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 (**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 (see screenshot).

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

#### 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. The steps are below.

**Steps:**

- In the beginning, select cell
**F5**. - Then, to calculate the
**Total**marks of**John**, type the formula below in the selected cell:

`=SUM(C5:E5)`

- Finally, press the
**Enter**key to get the result (see screenshot). - 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**(see screenshot).

- At this time, we will calculate the
**Average**marks of the students (**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**(see the screenshot below). - 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 Only for Cells with Values in Excel**

#### 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. Let’s see the steps below.

**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 (see screenshot).

- 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 Average a Column in Excel (7 Easy Methods)**

#### 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**. The steps to apply the functions are below.

**Steps:**

- In the first place, calculate the
**Total**marks (**F5:F10**) using the**SUM**function (described in the previous methods). - Now, to calculate the
**Average**of the**Total**marks which are greater than**245**, enter the formula below in cell**F11**:

`=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**. The steps to use this formula are below.

**Steps:**

- First, use the
**SUM**function to calculate the**Total**marks (**F5:F10**) like the previous methods. - After that, to calculate the
**Average**marks of the students, insert 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**.

**Read More:** **How to Average Values Greater Than Zero in Excel (4 Ways)**

### 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 function** and the **AVERAGE** function. The steps to use this formula are below.

**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}))`

- Eventually, 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:** **Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)**

## Conclusion

I hope the above tutorial will be helpful for you to calculate 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. Follow our website **ExcelDemy** to get more articles like this.

## Related Articles

**How to Average Every Nth Row in Excel (3 Ways)****Calculate Average of Averages in Excel (with Easy Steps)****How to Ignore #N/A Error When Getting Average in Excel****Calculate Average Percentage of Marks in Excel (Top 4 Methods)****How to Find Average of Specific Cells in Excel (3 Handy Ways)****Calculate Class Average in Excel (6 Easiest Methods)****How to Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)**