Dec 8 2008

# Summing Across Multiple Sheets

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.

1. Type “=SUM(” in the formula bar and goto step 2.
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.
3. While continuing to hold shift, select the last sheet in the range that you want to select.
4. 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.

### More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

### 26 Comments on this post

1. Multiple Sheets Sum - Learn Excel Tricks wrote:

[...] See the screen shots of  sum across multiple sheets at http://excelhints.com/2008/12/08/summing-across-multiple-sheets/ [...]

September 21st, 2013 at 2:34 pm
2. Summing Across Multiple Sheets wrote:

[…] Summing Across Multiple Sheets – Excel Hints – 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 … […]

March 14th, 2016 at 10:03 pm
1. sam said:

=sum(‘*’!A1)

December 14th, 2008 at 8:37 am
2. john said:

That’s another option if you want to sum every page in a workbook.

December 16th, 2008 at 9:56 pm
3. 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
4. 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
5. 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
6. 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
7. RHODA said:

I have a spreedsheet for the month of February for each day that has data for six diffrent items. I want to be able to have a Monthly Total sheet that will total up each time i input data into these six diffrent catagories. I am not sure how to do this can someone help me.

February 18th, 2011 at 5:27 pm
8. Darko said:

Hi Everyone,

I have a workbook with 12 worksheets in it. Each worksheet represents a month in 2011. Totals on each sheet are in different cells. I need to add up all the totals from the 12 different sheets … does anyone know how to do that.

Example: Sheet 1/Jan 2011 – total is in cell F113 …… Sheet 2/Feb 2011 – total is in cell F67 ……. and so on …… different cells in the different worksheets.

January 16th, 2012 at 9:37 am
9. Graeme said:

Hi

I am with Darko on this – HELP!

April 4th, 2012 at 7:28 am
10. Austin said:

I have an answer to Darko`s question.

Go to the sport were you want the sum to be and type =(
Then click the worksheet and the cell the total is in.
so now you have=(f113+
repeat then when your done press enter

April 19th, 2012 at 9:53 am
11. JIGAR said:

THIS IS A BIT COMPLICATED.
i have a excel with 5sheets,
1st is the summary sheet where data from the remaining 4sheets are summed against a particular cell.
eg:

summary sheet has following columns.

client, code, bill amt,

now the bill amt from all the 4 sheets are to be added in the summary sheet, against the client name.

the bill amts in all the 4 sheets are in different cell.

pls help……

July 10th, 2012 at 4:51 am
12. satheesh said:

I need to ad a Cell in multiple sheet. But sheets contains different types so i have to consolidate the same content in different sheet.

[ for an example.. in my excel i have 20 sheets among that 15 sheets are Regular jobs & remains are Hot Jobs.

I have to add all regular jobs in C8 cell. ]

October 3rd, 2012 at 5:04 am
13. Hisham said:

Dear All,

I want to use the start end method to sum up multiple sheets.i have copied a sheet within the range of (start End) and re neamed it, but when summing up the result is doubling the figure of new copied sheet? could you please help in this?

Thanks

February 18th, 2013 at 4:27 am
14. Anupam said:

I need the summation of the data from different sheets without specifying the sheet names in the formula. Is it possible?

April 18th, 2013 at 4:38 am
15. Gwen said:

I have two sheets in one workbook and want create a total sum on sheet two consisting of four amounts on sheet 1. I have tried this =sheet1!b3 and then it gives me the first figure correctly, however if I extend it and say =sheet1!b3:b6 then it gives me a value of 0. Any suggestions?

August 29th, 2013 at 1:46 am
16. ME said:

I have 12 spreadsheets, one for each month. I want to create a 13th spreadsheet to be the totals for the year. I am tracking monthly expenses and want to total without creating a formula on the 13th sheet for each individual cell.
Each of the 12 spreadsheets is set up EXACTLY the same in the way of labels for rows and columns but not specific data in the cells. I am totaling the rows and totaling the columns the exact same way in each of the monthly spreadsheets.
I want the 13th sheet to be exactly the same setup but want each cell in the 13th sheet to be totaling (summing) the same cell in the other 12 sheets. In other words putting the yearly totals into the corresponding cell of the 13th (yearly) sheet.
Do I have to create a formula in each cell of the 13th sheet or is there a way I can do this for the entire sheet at once?
Thanks.

March 26th, 2014 at 5:00 pm
17. Maszif said:

When I tried this approach on my time sheet it did not work. Figured out that, like most naming conventions within excel, the named sheet cannot have a space in it for example. My sheets are broken up into 53 weeks. Week 1 to Week 53. To get this formula to work I changed the first and last work sheet to: Week_1 and Week_53, eliminating the space. I did not have to do this with all of them, all though it my be prudent. so my final formula was the same =SUM(Week_1:Week_53!I7)

February 3rd, 2015 at 6:55 pm
18. Aj said:

can anyone tell me what the formula would be for (if there is one):
I have a workbook with multiple sheets. In “Unpaid – All Parcels” sheet I have different years list in various order in Column A, and amounts in Column C. In “Interest” sheet I have a list of years in descending order in Column A and percentage’s in ascending order in Column B.
I want a calculation to multiply Column C of “Unpaid – All Parcels” sheet with Column B of “Interest” sheet IF the value (year) in Column A of “Unpaid – All Parcels” sheet match the value (year) in Column A of “Interest” sheet.
I want this calculation to appear in Column D of “Unpaid – All Parcels”

March 13th, 2015 at 11:14 am
19. Oswaldo said:

This is great and incredibly useful, thanks for sharing it. You mentioned that this can be also used to add cells from multiple workbooks. Could you please explain how? Thanks !!!!

May 20th, 2015 at 6:09 pm

hi
i have 50 sheets and all the sheets are named (example: Mostafa Yehya El Baarini) i did the last sheet as total

For each sheet i have characteristics and in every characteristic i have the number of each characteristic (example: Mazy injector 5)

I want to Sum all numbers in all sheets of the mazy injector but in the sheet of total

Thank you

August 15th, 2015 at 5:37 am
21. sam said:

i was using =sum(Start:End!K1), but if one of any sheet deleted. It showing error.

October 23rd, 2015 at 4:55 am
22. sam said:

I am trying to create a hyperlink or formula that will generate an email but also include the file I am in as an attachment. Please help me.

October 27th, 2015 at 5:21 am
23. ExcelHints said:

Go to this forum and they will help you. (We own the forum). http://www.vbaexpress.com/forum/forum.php

November 6th, 2015 at 3:27 pm
24. Marc said:

My worksheet has 31 sheets plus a totals sheet, one for each day of the month. They are labeled 1, 2, 3, etc. For March of 2016, Monday will fall on sheet 7, 14, 21, 28. On my “Total” sheet, I need the data to SUM. Example. For each Monday during this month, I need the total count for Mondays only.

March 18th, 2016 at 3:24 pm
Subscribe Form