You probably know that you can use the Sum function to add up a range of cells. But did you know that you can specify a range that covers multiple tabs (worksheets) in your Excel workbook?
EXAMPLE
Let’s say you have a workbook that lists the month’s sales of each of your products. There is a row for each product and a column to shows sales for every location. Each month has its own tab in the workbook. In addition to the totals on each tab, you can easily enter a formula that will total the numbers from all of the worksheets (months).
Go to the tab that will hold the totals and click on the cell you want to first total to appear in. Now click the AutoSum button to start building your formula. Click on the tab for the first month you want included in the total but don’t select a cell yet. Instead, hold down the shift key on your keyboard and click on the last tab you want included in the total. Release the shift key. Both tabs and all the tabs in between will be highlighted. Now click on the cell(s) you want to have totaled. Press the Enter key to complete your formula. You will end up with a formula that looks something like =SUM(Sheet1:Sheet12!B2). This tells Excel to sum cell B2 on sheets 1 through 12.
You can now copy the formula just like any other formula to finish totaling all of the cells.
HOW THE RANGES WORK
In the formula above Sheet1:Sheet12! designates a range from Sheet1 through Sheet12. This range refers to the actual arrangement of the tabs in your workbook and is not affected by the numbers (1 and 12) that are part of the sheet names. For example, if you moved the tab named Sheet6 so it was to the right of Sheet12, it would not be included in the sum. Or, if you renamed Sheet4 as Sheet44, it would still be included in the sum as long as it was positioned between Sheet1 and Sheet12.
And of course you can do the same thing with other functions. This isn’t limited to Sum.
