Microsoft Office Tutorials and References
In Depth Information
SUM VISIBLE ROWS
Late-breaking Tip : To add up
cell B4 on all the worksheets
with Sales in the sheet name,
type =SUM(‘*Sales’!B4) and
press Enter.
Figure 41. A 3-D formula adds up all instances of B4 on
the 12 sheets from Jan through Dec.
If the ﬁ rst or last worksheet contains a space in the name, you have to use
apostrophes around the pair of worksheet names: =SUM(‘Jan 2009:Dec
2009’!B4) .
You can easily copy this formula to other cells on the summary worksheet.
Gotcha: Do not drag the summary worksheet to appear after the Jan worksheet,
or you will set up a circular reference.
Additional Details: It is possible to set up a named range that refers to a 3-D
range. Here is an interesting way to set up a named range:
Go to cell B4 on the Jan worksheet.
Select Insert, Name, Deﬁ ne.
The Refers To box contains =Jan!B4 . Click in the box. Hold down the Shift
key. Click on the Dec worksheet. The Refers To box changes to =Jan:
Dec!B4.
Summary: A 3-D formula can sum a speciﬁ c cell on several worksheets.
1.
2.
3.
SUM VISIBLE ROWS
Challenge: A SUM function totals all the cells in a range, whether they are hidden
or not. You want to sum only the visible rows.
Solution: You can use the SUBTOTAL function instead of SUM . The formula
you need is slightly different, depending on how you hid the rows.
If rows are hidden by using Format, Row, Hide, you use:
=SUBTOTAL(109,E2:E564)
This is an unusual use for SUBTOTAL . Normally, SUBTOTAL is used to force
Excel to ignore other SUBTOTAL cells within a range. SUBTOTAL can perform

Search JabSto ::

Custom Search