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.

SUM function in Excel, sum function and examples Picture 1

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

SUM function in Excel, sum function and examples Picture 2

- 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

SUM function in Excel, sum function and examples Picture 3

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.

SUM function in Excel, sum function and examples Picture 4

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).

SUM function in Excel, sum function and examples Picture 5

- Result:

SUM function in Excel, sum function and examples Picture 6

- You can scroll down to classes B, C, D and E to summarize the class sizes for 3 years.

SUM function in Excel, sum function and examples Picture 7

* 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)

SUM function in Excel, sum function and examples Picture 8

- Result:

SUM function in Excel, sum function and examples Picture 9

* 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.

SUM function in Excel, sum function and examples Picture 10

* 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.

SUM function in Excel, sum function and examples Picture 11

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:

SUM function in Excel, sum function and examples Picture 12

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.

Marvin Fry

Update 26 December 2024