Microsoft Office Tutorials and References
In Depth Information
Use the SUBTOTAL
function with FNUM
arguments to reference
information quickly in
SUM The SUM function adds all the numbers in a range of cells.
If an argument is an array or reference, only numbers in that array or reference are counted.
Empty cells, logical values, text, or error values in the array or reference are ignored. (See
the third example following.) Arguments that are error values or text that cannot be
translated into numbers cause errors.
A quick way to create a SUM formula with multiple reference arguments is to type “ =SUM( “ then click
the first cell or group of cells to be used in the formula. Then, press Shift+F8. Each range that you
highlight from that point will be added to the formula with the necessary comma separator for that
There are problems associated with the summing of numbers in Excel that you should be
aware of. Certain arguments can be coerced into numbers when directly entered into a SUM
function. SUM(4 , “2” , TRUE) equals 7 because the text value 2 is translated into the number 2,
and the logical value TRUE is translated into the number 1. However, if these same entries
are made in a range (B6:B8 as shown in Figure 10.25), the SUM function returns a value of 4
as calculated by the formula in C1 because Excel does not translate the entries in this case.
To further confuse the issue, Excel translates the entries if they are summed using the +
operator, as calculated by the formula in C2. These two forms of summing are useful and
complementar y, as long as you know the strengths and limitations of each form.
NUMBER The NUMBER arguments are 1 to 30 arguments for which you want
the total value or sum.
It is true that the SUM function can accept only 30 arguments, but there is a simple
workaround to this limitation. Each argument can contain up to 30 arguments of its own as
long as they are enclosed by parentheses. The following example illustrates how this is done.