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).

 [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

1st Sheet with Data

2nd Sheet with Data

2nd Sheet with Data

3rd Sheet with Data

3rd Sheet with Data

Sum of all 3 Sheets

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.  

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

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

Enter your email address: 

          

17 Comments on this post

Trackbacks

  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
  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.

    Thank you in advance

    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. ]

    Please advice.

    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

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories