Use the SUM Function
SUM(argument1, argument2, …)
When you use the SUM function, Excel adds the results of each of the arguments together and returns the result.
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
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.
This example has two arguments: 1 and 1. Excel adds them together and returns 2.
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 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 combines the arguments in examples 1, 2, 3, and 4 into a single function and returns the result, 63.
If a logical value is an argument, Excel recognizes it. Therefore, the SUM of 1 and TRUE is 2.
Excel cannot convert "two" to a number, so the function returns the error #VALUE.
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.
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.
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.