Summing Across Multiple Sheets
- 0 Comments
You might have come across an instance where you want to use a function against multiple sheets in a workbook. Excel provides an easy way to calculate aggregate functions across a range of sheets. In the examples below, you will see how this can be performed against any range of sheets and sheet names (or even multiple workbooks).
[ad#in-post-ad]
Examples
There are more ways to perform these functions than I will show here (including matching the data using formula and column and row headers). The point of this post is to show you the easiest way to sum data across multiple sheets.
The next three images will be the data that will be summed on the “Summary” sheet. We’ll take a look at the formula and how to enter it after the screenshots.
1st Sheet with Data
2nd Sheet with Data
3rd Sheet with Data
Sum of all 3 Sheets
Example Walk-Through
Let’s talk about the easiest way to get the formula entered. The quickest way is to enter the aggregate function you want to use (sum, average, count, etc.). In our example, we used the SUM formula.
- Type “=SUM(” in the formula bar and goto step 2.
- Next, you will want to hold down the shift key and select the first sheet in the range of sheets you want to select.
- While continuing to hold shift, select the last sheet in the range that you want to select.
- Finally, select the cell (or even range of cells) you want to be in the SUM. Hit enter you and you should see the result of the formula. It should look like the formula in the formula bar on the “Summary” tab.
In our example, cell A1 on the “Summary” sheet is the sum of A1 on all the sheets between “Data1″ and “Data3″. The formula is equivalent to Summary!A1 = Data1!A1 + Data2!A1 + Data3!A1 (or Summary!A1 = 10+100+1000 or 1110).
There are many variations of this formula and ways it can be used. Listed above is just one way to get started and become familiar with the uses of this method.
If you have any questions about the function or how to use it, please leave a comment in the comment form below.