Microsoft Office Tutorials and References

In Depth Information

**Examples of Math Functions**

ult. For example, =SUM(1,2,"3") will be 6, yet =SUM(D4:D6) in
Figure 11.1
res-

ults in 3.

The comma is treated as a union operator. If you replace the comma with a

space, Excel finds the cells that fall in the intersection of the selected

ranges. In cell E17, the formula of =SUM(F13:H14 G12:G15) adds up the two

cells that are in common between the two ranges.

If one cell in a referenced range contains an error, the result of the SUM

function is an error. To add numbers while ignoring error cells, use the

AGGREGATE function.

It is valid to create a spearing formula. This type of formula adds the

identical cell from many worksheets. For example, =SUM(Jan:Dec!B20) adds

cell B20 on all 12 sheets between Jan and Dec. If the sheet names contain

spaces or other nonalphabetic characters, surround the sheet names with

apostrophes: =SUM('Jan 2011:Dec 2011'!B20).

To quickly enter a SUM formula, you can press Alt+= or click the AutoSum

icon on the Formulas tab. In
Figure 11.2
,
clicking the AutoSum icon adds

totals to the 13 selected blank cells all at once (see
Figure 11.3
)
.