SUM function in Excel Help you calculate the total data easily. Here are details How to calculate the sum function of Microsoft Excel.
Microsoft Excel is part of the Microsoft office product kit. It helps users to statistical, synthesize and analyze data easily. Although there are many similar free tools, Excel still has a solid position, difficult to apply which spreadsheets can be replaced.
Excel is a “treasure” of a variety of tools and assets designed specifically for data analysis and processing. A familiar form of data processing is addition. A handy excel function to add the numbers is the SUM function. Therefore, Sum is probably the first function that every Excel users learn.
Although SUM is the leading function in this function, it is not the only Sum function in Excel. There are several different SUM functions in Excel, each with a unique point. This article will discuss the necessary SUM functions in Excel and how to use them.
These are the main contents in this article:
What is the reunion in Excel?
Simply put, the SUM function adds values. It is designed to return the SUM function of the user values, from individual values, scope, reference or combination of all three. The SUM function in Excel can handle well, up to 255 individually reference.
The formula of the SUM function in Excel
Knowing the formula of the SUM function in Excel can help you understand how the function of this function is. The function formula calculated in Excel as follows:
The SUM function has two structures so it can be written in two ways:
- = SUM (NUM1, NMM, … NUM_N). Here “Num1, Nm2, … Num_N” indicate the numbers we will calculate.
- = Sum (cellrange1, cellrange2, … cellrange_n). In there: “Cellrange1, Cellrange2, … cellrange_n” are digital columns.
The attention in suming calculations:
- SUM function can calculate decimal and fractions.
- In SUM calculation: = Sum (Num1, NM2, … Num_N)if you enter the “Num” parameter not the number, the SUM function will display the error #Name? Error.
- And similarly, in calculation = Sum (cellrange1, cellrange2, … cellrange_n) Contains not content, the SUM function will ignore that value.
- SUM function is not a dynamic function. Therefore, if you use the suming calculation into the sequence of cells and filter out some values, the output of the sum will not change with the nearest value. Therefore, it is better, you should use the subtotal function for this case.
Common reunion functions
The “members” of the SUM function in Excel have two things in common: one is that they calculate all values in a way, the second is that all have Sum in the name.
The first function, SUM only calculates the sum of the input and output values of the result. Other SUM functions perform similar tasks but there are some changes. For example, Sumif only adds specific conditions to meet the specific conditions. SUMPRODUCT deploys a arithmetic calculation on the array, then calculates the results.
Although the entire sum function is calculated, it is not their only features. Understand how to use other SUM functions can increase your work efficiency. You do not need to combine SUM with other excel functions, instead, just using SUM function to suit the needs is enough.
Some popular suming functions today:
- Sum is the most basic member in the function of Excel.
- Sumif calculates the total number of numbers that meet the specific requirements.
- Sumifs calculate the sum of many criteria.
- SUMPRODUCT multiply a scope of number, then calculate their sum.
Examples of SUM jaw
In the table above, there are four types of SUM function and below I will explain each specific example:
1. For example 1, total integers: = SUM (10,11,19) Meaning plus numbers 10,11,19 and 40 are the result 40.
2. For example 2, total decimal numbers: = SUM (10,2,9,6,2,4)simply the addition of 10.2+9.6+2.4 and results 25.8.
3. For example 3, total fractions: = SUM (4/2.8/2). The addition of numbers 4/2 and 8/2 and the result is 6.
4. For example, 4, the sum function is due to containing non -number content and that is also the reason for the calculation #Name? Error.
5. Use the second form of the function Sum [=SUM(B2:B10)] To calculate the sum of numbers in the sequence (B2: B10) Instead of parameters and give results 60.
Shortened SUM calculation:
Instead of writing calculations in a simple way, you can use the following:
First, choose the sequence and press “Alt +“And the result will display in the next cell.
Use other operations in SUM calculation
Other calculations such as (+, -, / and *) can be used in the SUM function. For example, = Sum (2*4) and the SUM function will produce 8. Similarly with the remaining calculations.
In fact, the SUM function will handle all numeric sets including numbers or calculations. Therefore, the first thing it will evaluate the results of the patching operations if there is no difference, it will produce the results.
Use SUM function along with other functions
1. It is possible to use the SUM function along with other functions. Here is an example of monthly average:
= SUM (A2: L2)/Counta (A2: L2)
2. SUM of A2: L2 divided by the number of months of the year in A2: L2 excluding the abandoned cell.
Use the SUM function on multiple pages
1 will be faulty.
= Sheet1! A1+Sheet2! A1+Sheet3! A1
Therefore, this problem will become easier to use 3D SUM function:
= SUM (Sheet1: Sheet3! A1)
The SUM function has calculated the sum of the A1 cells in all pages from 1 to 3.
This is very useful to calculate each application page of the months (January to December).
= Sum (January: December! A2) Plus all A2 cells on pages from January to December
This 3D method is also used the same in the Average, Min, Max, …
- = Average (sheet1: Sheet3! A1)
- = Min (sheet1: sheet3! A1)
- = Max (sheet1: sheet3! A1)
Common problems and solutions:
Case 1: Sum function display #####.
Please check the width of the column, because ##### the notice is not enough to display the results.
Case 2: SUM function displays letters and does not produce results. Or only number but not decimal.
Check the format of the column. Please enter Format and choose Number To choose the type you want. If after formatting, the box displays the letters does not change, you should press F2 and Enter.
Case 3: The function is not Japanese.
You must make sure Calculation is placed in mode Automatic. First Formulacustomized selection Calculation. Then press F9 to automatically update.
Case 4: Some values are not used in the SUM function.
Only valuable or sequence contains new numbers placed in the function. The empty cells, logical functions, or letters will be removed.
Case 5: The function produces #Name results? Error.
There was a spelling error here. For example, = Sue (A1: A10) instead of = SUM (A1: A10).
Common questions when using SUM jaw in Excel
How more/fewer decimal numbers? You can change the number format. Select the box or range mentioned and use Ctrl + 1 to open the Format Cells dialog box, then click the Number tab and select the format you want, make sure to indicate the decimal position you want.
How to add or subtract time? You can add and subtract time in a number of different ways. For example, to calculate the difference between 8 am and 12 noon for the purpose of calculating salary, you will use = (“12:00 pm”-“8:00 AM”)* Start time. Note, Excel calculates time as a fraction of a day, so you need to multiply 24 to calculate the total working hours. In the first example, use = ((B2-A2)+(D2-C2))*24 to calculate the total number of hours from the beginning to the end, subtract the lunch break (total 8.5 hours) .
How to get the difference between the date? Similar to time, you can add and subtract the date. This is an extremely popular example of how to calculate the number of days between two different days. The formula for using the SUM jaw in Excel here is very simple: = B2-A2 … “key” to process information here is to get the end/hour of the end and deducted date/hour.
How to calculate the sum of the display cells? Sometimes when you hide your own goods or use Autofillter to show certain data, you just want to calculate the display cells. You can use the subtotal function. If you are using a sum in the Excel table, any function you choose from the drop -down menu will automatically be entered as the subtotal.
See also:
