Ranges on Multiple Worksheets

When you need to refer to a range of cells, you enter the starting and ending point of the range, and you then separate those points by a colon. The following formula will give you the total of all cells in the range A1 through C4:.

=SUM(A1:C4)

What if you want to refer to a range of cells in multiple worksheets in your workbook. For example, how can you get a cell to return the sum of each cell A1 on the first three worksheets in your workbook? If the worksheets are named Alan1, Alan2 and Alan 3 then you would create the following formula:

=SUM(Alan1:Alan3!A1)

To calculate the sum of all cells in the range A1 through C5 on each worksheet, you would use the following formula:

=SUM(Alan1:Alan3!A1:C5)

This notation can be hard to remember. With the mouse, it is easy to build this formula with the following steps:

1. Click on the cell where you want to enter your formula.
2. Then enter an = sign and the first part of the function, followed by an opening parenthesis. For the examples given above, you would enter =SUM(.
3. Click on the sheet tab of the first sheet in the range.
4. Next hold down the Shift key as you click on the sheet tab of the last sheet in the range.
5. Use the mouse to select all the cells in the range on the visible worksheet.
6. Press Enter.

Alan Salmon