Summing Across Multiple Sheets
- 6 Comment
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).
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.
More Excel Tips
6 Comments on this post
Trackbacks
-
sam said:
=sum(‘*’!A1)
December 14th, 2008 at 8:37 am -
john said:
That’s another option if you want to sum every page in a workbook.
December 16th, 2008 at 9:56 pm -
teemup said:
If you want to have a dynamic range of sheets to be added in the aggregate sheet, you can name one sheet as Start and another one as End. Then you can use a formula like =sum(Start:End!A1). The two named sheets can be empty. This is very useful when you have a high number of sheets, let’s say 20 and you want to have the aggregate sum of for example sheets 5-17. Then you will only have to move the Start sheet before sheet 5 and the End sheet after sheet 17. It is very easy to change the range of sheets to be aggregated just by moving these two sheets and you won’t even have to touch any of the used formulas on the aggregate sheet.
January 9th, 2009 at 5:40 am -
john said:
Great comment teemup! That is definitely an excellent way to prevent from having to redo multiple formulas each time a sheet is added to the beginning or end of a range.
Thanks.
January 9th, 2009 at 10:47 am -
Jim said:
None of these suggestions work in Excel 2003; it adds the number of worksheets rather than the actual values in the referenced cells. Anyone have a working 2003 way to add across multiple worksheets?
February 2nd, 2009 at 3:36 pm -
john said:
Hi Jim –
I just confirmed that this does work in 2003. It works the same way in 2003 as in the example above. If you can provide more details about what problem you are having I can try to help you out.
Thanks – John
February 2nd, 2009 at 4:54 pm
Loading ...