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:

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

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

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

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

I get a circular error when I do this. Please help

I had this happen too until I took out any spaces from the tab name (Team 1 needed to be Team1). Then it worked as advertised.

=sum(‘sheet1:sheet3′!cell_address)

Thanks. It works

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

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

This was very helpful! Worked like it was explained. Thank you!

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

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

WHAT IF YOU WANT TO DRAG THE FUNCTION BECAUSE WHEN I DRAG IT USES ONLY THE CELL THAT I USED IN THE PREVIOUS WORKBOOK

Thanks, this was very and I mean very helpful

How do you copy from multiple sheets if it a formula you want to total? (Instead of just a number in the cell)

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)

Thanks It working very well

Thanks Asif for the appreciation.

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?

any body tell me

how to hide formula

bt i wanna formula will work depend afetr hide

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.

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

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.