How to Use the SUM Function in Excel
In this tutorial, you'll learn how to use the Excel SUM function to sum numbers.
The SUM function is one of the most commonly used functions in Excel. Many people know very little about Excel functions, but they may hear about this function somewhere.
The calculation to get the total is just a matter of adding up all the numerical data you need. In Excel, you can use the format "=A1+B1+C1+D1" to add up the data one cell at a time, but with the SUM function, you can specify a range and sum up all the values within that range.
Therefore, you can expect very efficient work, and you will not make mistakes such as forgetting to choose the value to sum.
SUM is a function to add individual values, values in a range of cells, or cell references of values.
The syntax of the SUM() function is as follows:
=SUM(number1, [number2], [number3], ...)
- Number1: The first number that you want to sum
- Number2: [optional]. The second value that you want to sum
- Number3: [optional]. The third value that you want to sum
- Arguments can be numbers, ranges, arrays, constants, cell references, and the results of the other functions or formulas. You can specify up to 255 arguments.
- SUM() sums only the cell that value is number, and ignores any cells containing text value.
SUM() Function Examples
Example 1: Adding a group of individual values
The following formula shows how to add individual values:
SUM() sums 1000, 250, and 100, and returns 1350.
Let's see more examples below.
Suppose you have the demo data, as shown below:
|1||Date||Item Code||Item Name||Qty||Unit Price||Amount|
|2||10/4/2019||Item001||Dell Laptop 1||2||1200||2400|
|3||10/4/2019||Item002||Dell Laptop 2||1||1240||1240|
|4||10/5/2019||Item003||Dell Laptop 3||3||1130||3390|
|5||10/5/2019||Item003||Dell Laptop 3||4||1130||4520|
|6||10/5/2019||Item002||Dell Laptop 2||5||1240||6200|
|7||10/6/2019||Item001||Dell Laptop 1||1||1120||1120|
|8||10/7/2019||Item001||Dell Laptop 1||7||1150||8050|
|9||10/9/2019||Item003||Dell Laptop 3||3||1100||3300|
|10||10/9/2019||Item002||Dell Laptop 2||8||1200||9600|
|11||10/10/2019||Item003||Dell Laptop 3||2||1000||2000|
Example 2: Adding values in a range of cells
Let's say you want to sum all the amounts in column F starting from cell F2 through F11.
The formula is as follows:
The formula returns 41820.
Example 3: Adding values in a range of cells and another cell reference of Value
You can add numbers in a range of cells and a cell reference of value, as the following example shows:
The formula sums all the amount of the item001 in column F and returns 11570.
SUM()ignores any cells that contain text value. It sums only the cells containing value numbers.
Example 3: Adding values in multiple ranges of cells
You can add values in multiple ranges of cells, as the following example shows:
=SUM(F2:F3, F7:F8, F4:F6)
In this tutorial, you've learned how to use the Excel SUM function to sum numbers.
You can use the SUMIF function to add individual values, values in a range, or cell references of values.
Note that the SUM function ignores any cells containing text values.
You will find that just by learning the SUM function, you can create various data you can frequently use in business situations.
If you need to sum values with a specified condition, use the SUMIF function.
Other functions include the SUMIFS function, which sums up the values that match multiple conditions within a range, and the SUMPRODUCT function, which calculates and sums the product of the corresponding elements of an array.