How to use the SUM function to calculate totals in Excel

SUM is a popular and very useful Excel function, and is also a basic arithmetic function. As its name suggests, the SUM function is used to calculate totals in Excel. And the parameters can be single parameters or ranges of cells. In this article, TipsMake.com will guide you to use the SUM function to calculate the sum in Excel, the common errors when calculating SUM by sum and how to fix it.

These are the main contents of this article:

1. SUM function definition in Excel
2. Structure of SUM function
3. Examples of SUM functions
4. Use other operations in the SUM calculation
5. Use SUM function with other functions
6. Use SUM functions across multiple pages
7. Common problems and solutions

SUM function definition in Excel

Excel defines SUM as a calculation of "total all numbers in the range". This means that the SUM function is responsible for calculating the sum of the numbers and parameters in the cells and range of cells.

Structure of SUM function

SUM has two structures so it can be written in two ways:

1. = SUM (num1, num2, . num_n). Here "num1, num2, . num_n" denotes the numbers we will calculate.
2. = SUM (CellRange1, CellRange2, . CellRange_n). Inside:   "CellRange1, CellRange2, . CellRange_n" are numeric columns.

Notes in the calculation SUM:

1. The SUM function can calculate decimals and fractions.

1. In the calculation SUM: = SUM (num1, num2, . num_n) , if you enter the parameter "num" not a number, the SUM function will display the #Name error ? Lỗi .

1. And similarly, in the calculation = SUM (CellRange1, CellRange2, . CellRange_n) contains non-numeric content, the SUM function will ignore that value.

1. The SUM function is not a dynamic function form. Therefore, if you use SUM calculation on a range of cells and then filter out some values, the output of the SUM calculation will not change according to the nearest value. Therefore, it is better to use the Subtotal function for this case.

Examples of SUM functions

In the table above, there are four types of SUM functions and below I will explain each specific example:

1. Example 1, the sum of integers: = SUM (10,11,19) means adding the numbers 10,11,19 and 40 as the result 40.

2. Example 2, the sum of the decimal numbers: = SUM (10.2,9.6,2,4) , simply add the sum of 10.2 + 9.6 + 2.4 and give the result 25.8.

3. Example 3, the sum of the fractional numbers: = SUM (4 / 2.8 / 2) . The calculation adds the numbers 4/2 and 8/2 and the result is 6.

4. Example 4, the SUM function fails because it contains non-numeric content and that is also the reason for the calculation to give #Name? Lỗi .

5. Use the second form of SUM [= SUM (B2: B10)] to sum the numbers in the sequence (B2: B10) instead of the parameters and produce the result 60.

Shortening of calculation SUM:

Instead of writing calculations in a simple way, you can use the following:

First, select the number range and press " Alt + " and the result will be displayed in the next box.

Use other operations in the SUM calculation

Other operations such as (+, -, / and *) can be used in SUM. For example, = SUM (2 * 4) and the SUM function will be given as 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 result of patch operation if there is no difference will result.

Use SUM function with other functions

1. It is possible to use the SUM function together with other functions. Here's an example of a monthly average:

= SUM (A2: L2) / COUNTA (A2: L2)

2. SUM of A2: L2 divided by the number of months in the year in A2: L2 does not include an empty cell.

Use SUM function on multiple sheets

1. Normally, to calculate the sum of cells with the same position in the pages, you will have to click on that box on each page and use the "+" between the values ​​but it will take time and calculations. will fail.

= Sheet1! A1 + Sheet2! A1 + Sheet3! A1

Therefore, this problem becomes easier when using the 3D SUM function:

= SUM (Sheet1: Sheet3! A1)

The SUM function summed cells A1 in all pages 1 through 3.

This is very useful for calculating single pages of months (January to December).

= SUM (January: December! A2) has added all A2 cells on each page from January to December

This 3D method is similarly used in the functions AVERAGE, MIN, MAX, .

1. = AVERAGE (Sheet1: Sheet3! A1)
2. = MIN (Sheet1: Sheet3! A1)
3. = MAX (Sheet1: Sheet3! A1)

Common issues and solutions:

Case 1: The SUM function displays #####.

Check the column width, because ##### notifies enough space to display results.

Case 2: The SUM function displays the word and does not produce the result. Or show only numbers but not decimal numbers.

Check the format of the column. Go to Format and select Number to select the format you want. If after formatting, the text display does not change, you should press F2 and enter.

Case 3: The SUM function is not updated.

You must make sure that Calculation is set to Automatic . First go to Formula , select the Calculation option. Then press F9 to automatically update the page.

Case 4: Some values ​​are not used in SUM function.

Only the value or range containing the new number is placed in the function. Empty cells, logic functions, or text will be removed.

Case 5: Function that outputs #NAME? Lỗi.

There was a spelling error here. For example, = sume (A1: A10) instead of = SUM (A1: A10).

See more:

1. How to combine Vlookup function with If function in Excel
2. How to use the Power function in Excel
3. How to use Hlookup function on Excel
4. How to use Excel's VALUE function
5. How to use Vlookup function in Excel