Sum Across Multiple Sheets in Microsoft Excel

 

In this article we will learn how to add cells from different sheets in Microsoft Excel.

Consider a scenario while working on the reports you want a formula that will give you the total from the same cell address in different sheets.

We will use SUM function to retrieve the output.

Sum: Adds all the numbers in a range of cells.

Syntax: =SUM(number1,number2,…)

There can be maximum 255 arguments. Refer below shown screenshot:

img1
 
Let us take an example to add totals from different sheets:

We have three sheets named as Jan, Feb & Mar.

img2
 

  • In cell A1, a number is stored in all the sheets & we want to take a sum.
  • Let us enter number 10 in cell A1 in all the sheets to get the output as 30.
  • We can put the formula in any cell in any sheet.
  • Type =SUM in any cell in any of the sheet in the workbook.
  • Select the tab for the first sheet like Jan.
  • Hold the shift key and then select the tab for the last sheet, Mar.
  • Click on the specific cell i.e. cell A1 for which you want to take a sum.
  • The formula would be =SUM(Jan:Mar!A1)
  • Then press Enter. We will get the total from cells in different sheets.

img3
 
image 48

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 
 



23 thoughts on “Sum Across Multiple Sheets in Microsoft Excel

  1. How do you add numbers in cells in different positions on different sheets? I thought I was doing so, but end up with some cells being automatically selected on subsequent sheets (often empty ones, but not always).

    • How do you add numbers in cells in different positions on different sheets? I thought I was doing so, but end up with some cells being automatically selected on subsequent sheets (often empty ones, but not always).

  2. my question is in my office every day some data enter in day one and anther day second sheet ext. totally 30 day 30 sheet per month my question i want report totally particular sheet in display

  3. i have 5 sales man whose selling data is put in one mixed column per day randomly ( not neccesary their sale amount is one by one in a column ).
    now i want to auto update their personal selling automatically in another excell sheet while i enter any salesman selling amount in first page with his name.

    please need help through e-mail as soon as possible.

    • Hi,

      Thanks for contacting us!

      Please post your query @ http://www.excelforum.com. There you can attach Excel file so it would be easier to understand the query. And if you can share the excel file then we will resolve the query here only.

      Thanks
      Excel Tip & Excel forum team

  4. What if the total in the separate work sheets are not in the same cell on each worksheet?

    For example some are in C38 and some are in C43 and so on..

    How do I get the totals from each worksheet?

    • Hi Jannae,

      In case you are working with 2 sheets i.e. Sheet1 & Sheet2.
      Case#1: Cell C38 & C43 in both sheets has values; to get total of these values we can use formula =SUM(Sheet1!C38,Sheet1!C43,Sheet2!C38,Sheet2!C43)

      Case#2: Cell C38 has some value in both sheets while cell C43 has value in Sheet1 only in that scenario the formula =SUM(Sheet1!C38,Sheet1!C43,Sheet2!C38)

      Regards,
      Ashish

  5. It works, thanks! Another tip if I may. If you name your sheet as numbers (1,2,3,…) the formula should be like this: =SUM(‘(SHEET1):(SHEET2)’!CELL NUMBER)
    example:
    =SUM(‘(1):(27)’!M39)

  6. I would like to use the =SUM(Sheet1:Sheet15!) type sum for a range of worksheets, but not the same cells on each worksheet. I see above this can obviously be done by typing them in separately, however, I would like to use the range of worksheets. This is because I often add more tabs in between Sheet1 and Sheet15 and I want all my SUM formulas to include these new tabs without having to change every formula. This is a very large excel file with a LOT of formulas that would need to be changed every time. Any help?

    • Hi Mohit Agarwal,

      For hiding the formula, select the range / cells containing the formulas that need protection. Right click and choose format cells, then go to the Protection Tab. In Protection Tab, select LOCKED and HIDDEN check box and click on OK (Locked check box restricts editing of cell). Then, press the combination of Alt + T + P + P keys, a Protect Sheet option window will appear. Apply password, if required and click on OK.

  7. I have this formula =’1-1′!B1 that links the total on sheet 1-1 to the main sheet. How do I copy the formula so it link like the totals on sheets 1-2, 1-3 1-4 all the way to 12-31? (For the whole year)? All the to totals are on Cell B1on each sheet. The only thing that changes is the sheet number: 1-1, 1-2, 1-3 etc..

  8. I have a small store of materials. and having about 300 employees, who use to take material on daily base. i have made different sheets for each employee. there are about 30 materials. and the materials are repeated for each of employee several times.

    i need a general format in excel, that can calculate by looking exact material name and just sum it up for all the 300 employees. and show me the exact issued amount of each material individual.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>