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.


5 ★ | 1 Vote

May be interested

  • PMT function in Excel - Usage and examplesPMT function in Excel - Usage and examples
    the 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 examplesGAMMAINV function in Excel, how to use and examples
    the 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 examplesHow to use the MAXA function in Excel, detailed examples
    the 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, 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 ExcelHow to use the SUMIF function in Excel
    the 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
    choose 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 examplesDCOUNT function in Excel - Usage and practical examples
    the 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
    the 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
    random 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
    find 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