SUM function in Excel, sum function and examples
In Excel spreadsheets, the SUM function is commonly used to calculate the sum of a range of cells, an entire column, or non-adjacent cells. In the article below, TipsMake will summarize the concepts, formulas, and examples of how to use the SUM function in Excel to calculate simple sums or satisfy certain criteria. Readers should refer to it to increase their knowledge and skills in using Excel.
What is the SUM function used for? How to calculate the SUM function in Excel
Article content:
1. What is the SUM function? Application of the SUM function in EXCEL
2. Syntax of the SUM function in EXCEL
3. Specific examples of using the SUM function in EXCEL
4. Errors when using the SUM function in EXCEL
1. What is the SUM function? Applications of the SUM function in Excel
The SUM function in Excel adds the numeric values in a range of cells. When combined with other functions, the Sum function is used to sum data that meets certain conditions. The values supplied to the function can be numbers, cell references, or ranges.
2. SUM function syntax in EXCEL
- Syntax : =SUM (number 1, number 2, …)
- Function: Calculate the sum of number 1, number 2, …
- In which: number 1, number 2, … are the arguments you want to calculate the sum of.
Note:
- Cells with logical value TRUE are considered as 1, FALSE are considered as 0.
- If the argument is an array or reference, only the numeric values in that array or reference are counted. Other values in the array or reference are ignored.
3. Specific example using the SUM function in EXCEL
* Example 1:
=SUM (1, 2, 3) has the value 6.
=SUM (2, 3, TRUE) has the value 6.
=SUM ('2','3',1) has the value 6.
* Example 2: We have the following employee salary table
- Apply the SUM function to calculate the total salary of employees in the month in the actual salary column
- Use the formula: F10=SUM (F5:F9) to select all values from cells F5 to F9. We have the result as shown below
So you have the total salary to pay to employees.
* Example 3: Calculate the sum of the same cell on multiple Sheets.
In this example, TipsMake will calculate the total number of students for the school years from 2020 to 2022. In Sheets 2020, 2021, 2022 and Total, there will be the same table and all start from class A in column B5 and class A's number of students in column C5.
Step 2: In the Total Sheet, at class A's number of students, column C5, enter the formula:
=SUM('2020'!C5,'2021'!C5,'2022'!C5).
- Result:
- You can scroll down to classes B, C, D and E to summarize the class sizes for 3 years.
* Example 4: Calculate the sum of a range on multiple Sheets.
Use the student enrollment table by years. Here you can calculate the total number of students for all 3 years using the following formula:
=SUM('2020'!C5:C9,'2021'!C5:C9,'2022'!C5:C9)
- Result:
* Example 4: Conditional sum function
In addition to calculating the total of rows and cells in a sheet in the usual way, the SUMIF function in Excel returns a conditional sum.
For example, the SUMIF function below sums the values in the range B1:B5 if the corresponding cells in the range A1:A5 contain exactly the color green.
* Example 5: Combining the SUM function with other functions
When combined with different rows, the SUM function is used to calculate the sum of a series that satisfies certain conditions.
For example, the SUM formula below uses SUM and LARGE to sum the largest numbers in a range. Change {1,2,3,4} to {1,2,3,4,5} to sum the 5 largest numbers.
4. Errors when using the SUM function in EXCEL
While using the SUM function to calculate the sum, you may encounter the #value error .
- The #value error in the excel sum function occurs if any of the arguments supplied directly to the SUM function cannot be interpreted as numeric values.
Note : If a supplied cell data range contains values that cannot be interpreted as numeric values, the SUM function ignores it.
What values are fed into the SUM function for calculation?
Numeric and date values are treated as numeric values in the Excel SUM function. However, text and logical values are treated differently, depending on whether the value is a value stored in spreadsheet cells or supplied directly to the SUM function.
The table below summarizes the values used in the Excel SUM function, and the values that are ignored or that cause errors:
If the SUM function references a cell that contains the #VALUE! error, the formula returns the #VALUE! error.
To fix the #VALUE! error , simply build a formula that ignores the reference range containing the error and sums the remaining 'normal' values.
To implement this scenario, you use a combination of the SUM function with the IF function and the ISERROR function to determine if there is an error in a specific reference range. This scenario requires an array formula:
=SUM(IF(ISERROR(B2:D2),"",B2:D2))
Note: since this is an array formula, you will have to enter it by pressing the CTRL + SHIFT + ENTER keys . Excel will automatically wrap the formula in braces {} . If you try to enter these formulas automatically, Excel will display the formula as text.
In addition, the above function can be used to fix #VALUE!, #N/A, #NULL, #p/0!, and some other SUM function errors.
You have just learned the SUM function in Excel, its operation is extremely simple. In addition, you should learn some more functions to calculate the sum with given conditions such as the SUMIF function in Excel to perform calculations on spreadsheets with problems that combine multiple conditions to get the desired results.
The Left function is a function that separates the left character string or is combined with other functions including the Sum function and other search functions such as Vlookup, Hlookup. You can refer to the Left function tutorial article that TipsMake has shared.
You should read it
- How to fix Vietnamese typing error on Skype
- Young man has a close relationship with many famous people thanks to ... Photoshop resources
- How to disable Touch ID and Face ID on iOS 11 to enhance security
- Acer unveiled a new netbook
- Codename of Windows versions
- Conficker controls 4% of globally infected PCs
- Compare 2 websites to download Linux Flathub and Snap Store applications
- How to Change Your Password in Windows 8
May be interested
- How to fix the error of the Sum function not adding in Excel successfullyhow to fix the error of the sum function not adding in excel? find out how to fix it in this detailed article so that your work is not interrupted.
- Crack Excel password, remove Sheet password in Excel filedon't remember your excel password and want to find a way to recover it? learn the trick to easily crack excel password, helping you quickly regain access.
- Shortcut keys to quickly hide and show rows and columns in Excelsometimes you need to hide rows in excel to clean up your spreadsheet. instead of doing it manually, learn how to use the shortcut key to hide rows, which will save you time and improve your work efficiency.
- Create a PowerPoint presentation from an existing Word documentcreating powerpoint presentations from existing word documents helps you create presentation slides faster and easier than usual. to learn how to do this, please follow the steps below.
- PowerPoint presentation with keyboard, slide shortcutspresenting powerpoint using the keyboard is a great trick to help you control your slides. discover useful keyboard shortcuts in this article.
- How to create notes on your computer screen using Sticky Notesif you need to take notes while working on your computer, but don't want to open additional application windows, try creating notes on your computer screen with sticky notes, which helps you remember all important information.