Microsoft Office Tutorials and References
In Depth Information
Summing Stuff on Different Worksheets
To find out how to insert a new worksheet, refer to this chapter’s “Don’t
Short-Sheet Me!” section. To find out how to rename a sheet tab, read
the earlier “A worksheet by any other name . . .” section.
2. Next, I enter the worksheet title Mother Goose Enterprises – Total
Projected Income 2014 in cell A1.
Do this by selecting cell A1 and then typing the text.
3. Finally, I copy the rest of the row headings for column A (containing the
revenue and expense descriptions) from the Sprat Diet Ctr worksheet to
the Total Income worksheet.
To do this, select cell A3 in the Total Income sheet and then click the
Sprat Diet Ctr tab. Select the cell range A3:A22 in this sheet; then press
Ctrl+C, click the Total Income tab again, and press Enter.
I am now ready to create the master SUM formula that totals the revenues of
all nine companies in cell B3 of the Total Income sheet:
1. I start by clicking cell B3 and pressing Alt+= to select the AutoSum feature.
Excel then puts =SUM( ) in the cell with the insertion point placed
between the two parentheses.
2. I click the Sprat Diet Ctr sheet tab, and then click its cell B3 to select the
projected revenues for the Jack Sprat Diet Centers.
The Formula bar reads =SUM(‘Sprat Diet Ctr’!B3) after selecting
3. Next, I type a comma (,) — the comma starts a new argument. I click the
J&J Trauma Ctr sheet tab and then click its cell B3 to select projected
revenues for the Jack and Jill Trauma Centers.
The Formula bar now reads =SUM(‘Sprat Diet Ctr’!B3,‘J&J
Trauma Ctr’!B3) after I select this cell.
4. I continue in this manner, typing a comma (to start a new argument) and
then selecting cell B3 with the projected revenues for all the other
companies in the following seven sheets.
At the end of this procedure, the Formula bar now appears with the
whopping SUM formula shown on the Formula bar in Figure 7-12.
5. To complete the SUM formula in cell B3 of the Total Income worksheet,
I then click the Enter box in the Formula bar (I could press Enter on my
keyboard, as well).
In Figure 7-12, note the result in cell B3. As you can see in the Formula
bar, the master SUM formula that returns 6,681,450.78 to cell B3 of the
Total Income worksheet gets its result by summing the values in B3 in all
nine of the supporting worksheets.