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
- PMT function in Excel - Usage and examplesthe pmt function is one of the built-in financial functions of the excel software used to calculate the payment for a loan based on regular payments and a constant interest rate. the pmt function is not only useful for businesses, but also very practical for users if you want to calculate a loan.
- GAMMAINV function in Excel, how to use and examplesthe gammainv function in excel helps calculate the inverse value of the cumulative gamma distribution, which is used in statistics and data analysis. this article will guide you on how to use the gammainv function with specific illustrative examples.
- How to use the MAXA function in Excel, detailed examplesthe maxa function in excel helps find the largest value in a data set, including numbers, logical values, and text containing numbers. this is different from the max function, which only counts numbers. let's learn the syntax and usage of the maxa function with practical examples.
- LEFT function in Excel, how to use LEFT function and illustrative examplesleft function in excel, how to use left function and illustrative examples. left function in excel is a function of string processing function, you use left function when you need to cut the character string to the left of the text string. if you need to learn more about mid functions, be patient
- How to use the SUMIF function in Excelthe sumif function in excel is a function used to compute values in a specified range. the sumif function can be used for summing cells based on the date, data and text that are connected to the specified area.
- Choose function in Excel, how to use the Choose function and illustrative exampleschoose function in excel, how to use the choose function and illustrative examples. you are looking for ways to use the choose function in excel so you can use the choose function quickly. so, please refer to the following article to know the choose function syntax, for example how
- DCOUNT function in Excel - Usage and practical examplesthe dcounta function is one of many frequently used math functions in excel. the dcount function helps you count non-blank data cells in list columns or data arrays with defined conditions.
- The function takes whole parts in Excel - Specific examplesthe function takes whole parts in excel - specific examples. excel has 2 functions that involve taking integer parts: quotient, int. there are many people who have a confusion between these two functions, only when you understand the nature of each one can you use them most accurately.
- Random function in Excel (RAND function), how to use the Random function and examplesrandom function in excel (rand function), how to use the random function and examples. you need to get a random value in a range of values. to avoid duplicate values and objectivity, you should use the rand () function. the article below gi
- FIND function in Excel - Usage and examplesfind function in excel - usage and examples. the find function in excel is a function of the text function group, you can use the find function to find the position of a substring of text in another text. to understand more about the find function, how to use it and the examples used in the find function