What is the Excel SUM Function?
Excel SUM function is used to add all numbers stored in the function argument. The SUM function argument can be a number, logical values, range, cell, array or even an excel formula.
SUM Syntax
=SUM(number1, [number2], …)
number1 is mandatory argument. SUM function can accommodate up to 255 arguments, argument number2 until number255 is optional.
If the argument is a cell, range or array, then the SUM function only calculates cell, range or array containing numbers. SUM function will ignore the text, blank space, and logical value.
If text or logical value is typed directly inside the SUM function argument, then excel will attempt to convert it, if successfully, it will be calculated by the SUM function, if not will generate an error.
If one of the arguments is an error, then the SUM function will result in an error too.
How to Use SUM Function in Excel
For example, there are data such as the image below.
The 1st data until the 3rd data is the number, one negative number, and two positive numbers.
The 4th data is text, although its appearance is number 5, excel considers it as text. It’s the left-align numbers; this is the default format for all text data in excel. The number stored as text is known as the alphanumeric.
The 5th data is text.
The 6th data is the logical value TRUE. Only two values, other than TRUE is FALSE.
The 7th data is an error.
How are the results of the existing SUM functions in column C (see image below).
SUM Function #1
=SUM(3,2)
The SUM function contains two arguments; both are number typed directly in the SUM function argument. It’s just like adding two numbers. The result is 5.
SUM Function #2
=SUM(30,"5",TRUE)
SUM function contains 3 arguments. All three have different types. A first argument is a number, the second argument is a text (marked with double quotes flanking it), and the third argument is a logical value.
What is the sum of the three arguments? Since all data is typed directly within the SUM function, Excel will attempt to convert non-number data into numbers.
The second argument is the number five “5” stored as text. “5” is a “number”; then excel can convert it to number 5.
The third argument is a logical value. If involved in mathematical operations excel will convert TRUE to number 1. Otherwise, Excel will convert FALSE to number 0.
The result of the calculations is =30+5+1. The result is 36.
SUM Function #3
=SUM(30,"5",TRUE,"lima")
Same as the SUM function #2 but added one more text argument. Unlike the “5” argument, Excel will convert it to a number; the “five” argument cannot be converted into a number since it is a text data.
Since excel cannot convert it to a number, then the result is error #VALUE, although the problem is only one of four arguments.
SUM Function #4
=SUM(A1:A6)
SUM function only contains one argument, range A1:A6. What data is stored in the range A1:A6? The first three data, A1, A2 and A3 are numbers, the next two data are text with one of them is alphanumeric, and the last data is a logical value.
If any text or the logical value stored within the range and SUM function use it as an argument, then all of them will be ignored by SUM function.
So, SUM function only adds up to 3 data only, that is the first three data. The result is 40.
SUM Function #5
=SUM(A1:A7)
Just like SUM function #4, the SUM function contains one argument of range, but the range used up to cell A7, error #DIV/0. Although there is only one error in a cell, the SUM function will generate error too. The result is error #DIV/0.
SUM Function #6
=SUM(A1:A3,15)
SUM function can have a different type of arguments in one function. The SUM function #6 has two arguments, the first argument is a range and a second argument is a number. SUM function will only sum the numbers within the range and number in the second argument, =-5+15+30+15. The result is 55.
SUM Function #7
=SUM(A3:A6)
Just like SUM function #5, the SUM function has a range argument. Range A3:A6 has a different type of data. There are 4 data, 1 number, 2 texts and 1 logical value. SUM function will add up the first data, i.e., the number 30. The result is 30.
For more details, please see the results of SUM function above in the picture below.
SUM Example
For example, there are data such as the image below.
There are three areas with iPhone sales data. What is the total of iPhone sales in all regions?
There are several solutions, but the best solution is to use the SUM function with one range argument. Why? It will save a lot of the time. No need to type all cell addresses that contain numbers.
The range used as the SUM function argument is the range C2:C14, containing all sales data.
Here are the steps how to write the SUM formula:
- Place the cursor in cell C15
- Type =SUM(
- Move the cursor to cell C2, the start of the range
- Press the SHIFT key and hold
- Move the cursor to cell C14, the end of the range and release the SHIFT key
- Type close brackets “)”
- Press the ENTER key
The result is as shown below
Another way #1, using keyboard and mouse
Steps to write a SUM formula above everything using the keyboard. There is another way by using keyboard and mouse. Here are the steps:
- Place the cursor in cell C15 by clicking cell C15
- Type =SUM(
- Click cell C2
- Press the SHIFT key and hold
- Click cell C14 then releases the SHIFT key
- Type close brackets “)”
- Press the ENTER button
The result is the same as before.
Another way #2, using AutoSUM shortcut (ALT+=)
Excel has a shortcut to automatically writing a SUM formula. The shortcut is ALT+= (ALT key and equal “=” key pressed together), known as AutoSUM shortcut. How to use autosum in excel, here are the steps:
- Place the cursor in cell C15
- Press the ALT key and the equal “=” key at the same time
The result is as shown below.
SUM function automatically appears along with the range address that fills the first argument. Check the range address, If right then press the ENTER key. The result is the same as before.
Another way #3, using Ribbon Menu
The Ribbon menu has an AutoSUM button; its location is in the “Home” tab, the “Editing” group, the right-most position in the Home tab.
How to use it is like how to use the AutoSUM shortcut. Place the cursor in cell C15, the total sales location cell, then click AutoSUM.
SUM function automatically appears following the address range as the first argument. Check the range address, If right then press the ENTER key.
The advantages of using the Ribbon Menu there are many choices of functions. The short video above shows not only the SUM function available to use. There is the AVERAGE, COUNT, MAX and MIN function.
In addition to using the mouse, AutoSUM menu in the ribbon menu can be accessed using the shortcut /, H, U, S (Press the fourth button in sequence, not simultaneously)
