Excel SUM Function – Five Methods To Use

Excel SUM Function – Definition


Excel SUM Function makes the sum of numeric values in a cell or range of the cells. Which including numbers, cell references, ranges, arrays and skip text values or blank cells.


The Syntax of Excel SUM Function


=SUM (number1, [number2], ….]

Argument Explanation


number1 = this is the first number we want to add. This number may be like (8) any numeric value or a cell reference (A2) or a range of cells like (A2: A10)

number2– 255 (Optional) – This is the second number we may add after the first number. And in the same way, we can add up to 255 numbers in this way.

This function returns


Excel SUM function always returns a numeric value.


The Short-Cut Method for Excel SUM Function


Just select the data and press “ALT + =” together.

Select one more blank row, where the function will make the total of the data.

Select the column data which we want to sum, go to home tab –editing tab- press AutoSum button for quick addition. See image below:-

Excel SUM Function


Important Note


This function will count only numeric values and skip the following arguments:-

An empty cell, text value or logical values.

If there is an error in a cell or range like #N/A, #REF! #VALUE! This function will display the result as an error value.

See in the below-given image; we have captured five examples of the Excel SUM Function.  

And here, we can see a data-set and some examples with their Formula entered and result.


Excel SUM Function


Now we will explain the examples as mentioned above in detail below.


Using Number Value as an Argument


In the above-given data-set, we will explain each of the below-given examples.

In the 1st example, from above data-set, we have entered the numeric value within the formula. See “=SUM (2,3,4)” and hit enter and see the result is “9”. So this is the first method to input values within the formula.


Using Separate Ranges for Columns


In the data-set, the values put in two columns, and their range is “A1: A3” and “B1: B3”. Just consider these range as number1 and number2.

Now we will put these ranges as it is within the formula and see: – “=SUM (A1: A3, B1: B3)” and the result is “24”.


Using Single Range For The Whole Data-Set


In the previous case, our data-set was in two columns. But now we can make it a whole data-set as a single range as “A1: B3”.

And now put this whole range within the formula like: – “=SUM (A1: B3)” and the result is the same as in the previous example.


Using Numbers and Cell Reference Together


This example is a bit tricky example but not complicated. This example is not part of the above data-set.

Arguments are in this example: – “=SUM (“2”, 2, A1, TRUE)”

The number is “2”, text value (“double quotes”), but when press enter it will convert this as a number value.

The second number is two again number value.

The third number is a cell reference (A1), which is a number value in the data-set

Forth is a Boolean value as “TRUE,” and value of “TRUE” in excel is “1”.

So there is four number in this example, and I have explained all here.

Hit enter, and it will give us the result as “7”. How to see below:-

“2” converts as number value 2

2 is again a number value

“A1” the value of this in the data-set is 2.

“TRUE” – in excel true has value as 1

So (2+2+2+1) =7.


Using Mathematical Operators


In this example, we have applied the mathematical operators, such as (+, -, /,*). Excel SUM function provides the result correctly as its job.

How does this work? The very first thing it will calculate the numbers according to its operator. And same with other arguments also if they have any operators. Then after that, it makes a sum of all the numeric values.


Related Posts:

Excel COUNT Function, Excel COUNTIF Function, Excel COUNTIFS Function, Excel COUNTA Function


Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.