No Comments

Use the SUM Function

Author: Sharlie Last modified: October 23 2015

The SUM function adds. It can include up to 255 . An argument can be a number, a name, an array, a formula, a logical value, a function, or

Syntax:

SUM(argument1, argument2, …)

When you use the SUM function, Excel adds the results of each of the arguments together and returns the result.

When using the SUM function, how Excel treats a data type may depend on whether it is located in a cell, an array, or an argument. TRUE and FALSE are logical values. Refer to Use Excel Formulas to Make Comparisons or Join Text to learn more about logical values. In a cell, Excel considers a number preceded by an apostrophe text. In a formula or a function, Excel considers a number enclosed in double quotes text.

If a cell contains a logical value, text, or an error value Excel ignores the cell. If an array contains a logical value, text, or an error value, Excel ignores the logical value, text, or error value. If an argument contains a logical value or text, Excel includes it in the calculation. TRUE becomes 1, FALSE becomes 0, and text causes an error if Excel cannot convert the text to a number. For example, = SUM(TRUE, FALSE, TRUE) returns 2, =SUM(“One”, “1”) returns the error #VALUE!, and =SUM(“1”, “1”) returns 2.

In the following worksheet, all of the cells that have orange text contain a calculation. All of the calculations use the SUM function. Feel free to modify the worksheet and to create your own functions.

The Advanced Examples Tab Explained

Example 1

Excel evaluates cells A1:A4: 3, TRUE, 2, and 1. It ignores cell A2, TRUE, because it is a logical value. It adds the values in cells A1, A3, and A4: 3, 2, and 1, and returns 6.

Example 2

This example has two arguments: 1 and 1. Excel adds them together and returns 2.

Example 3

Cells B2:B6 are named SampleData. Excel adds cells B2, B4, and B6: 5, 2, and 4, and returns 11. It ignores cell B3, because Excel considers a number preceded by an apostrophe text. It ignores cell B5, because it is text.

Example 4

Example 4 contains an array. Excel multiplies 2 times 5, gets 10; 3 times 5, gets 15; and 4 times 5 gets 20. It then adds those results together and returns 45.

Example 5

Example 5 combines the arguments in examples 1, 2, 3, and 4 into a single function and returns the result, 63.

Example 6

If a logical value is an argument, Excel recognizes it. Therefore, the SUM of 1 and TRUE is 2.

Example 7

Excel cannot convert "two" to a number, so the function returns the error #VALUE.

Example 8

The argument "3" is text, but Excel can convert it to the number 3. When text can be converted to a number and is used as an argument, Excel recognizes it. The function adds 5, 3, 2, and 3 and returns 13.

Tip

You can also use AutoSum to access the SUM function. When you access the SUM function through AutoSum, Excel provides you with extra features that enable you to calculate a sum quickly.

A reference is a cell address, a range, a cell name, a range name, or a named constant.

Arguments are the values you provide a function with so that it can perform a calculation.

An array is a list of values, enclosed in curly braces, where a comma separates each column, and a semi-colon separates each row.

In a cell, if a number is preceded by an apostrophe ('), it is considered text.

In an array, if a number is enclosed in double quotes ("), it is considered text.

In a formula, if Excel expects a number, but encounters text, if the text is in a format that Excel accepts as a number, Excel converts it to a number.

Text in the format of a number is called a text representation of a number.