Microsoft Office Tutorials and References

In Depth Information

**SUM A CELL THROUGH SEVERAL WORKSHEETS**

also creates ﬁ ve range names for the headings in row 1. For example, the

GP range refers to D2:D13.

You can now easily sum a range by using
=SUM(Sales)
or

=SUM(Atlanta,Charlotte).
However, if you include a space between the

named ranges, Excel includes only the cells at the intersection of the two ranges.

The formula
=SUM(Boston COGS)
ﬁ nds the one cell at the intersection of the

Boston range (B3:F3) and the COGS range (C2:C13). Only one cell is in common

between these two ranges, so the result is the 88,351 found in cell C3.

Part

I

Figure 40.
Excel creates i ve named ranges in this selection.

Summary:
When you use a space between arguments in a
SUM
function, Excel

returns only the intersection of the ranges.

SUM A CELL THROUGH

SEVERAL WORKSHEETS

Challenge:
You have 12 identical worksheets, one for each month. You would

like to summarize each worksheet. Is there a better way than using
=Jan!B4

+Feb!B4+Mar!B4+Apr!B4
…?

Solution:
You can use a 3-D formula such as
=SUM(Jan:Dec!B4),
as shown

in Figure 41.