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.
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
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
Solution: You can use a 3-D formula such as =SUM(Jan:Dec!B4), as shown
in Figure 41.