Sagufta Tarannum
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)
