Pivot tables are brilliant for slicing and dicing data, but sometimes a simpler approach works just fine.

Suppose you have an Excel workbook with four worksheets, each holding the sales figure for a quarter in cell B1. The worksheets are named Quarter 1, Quarter 2, Quarter 3 and Quarter 4.

You then create a worksheet called ‘Year’ that adds the four quarters together to give you the sales for the year.

On your ‘Year’ worksheet, you could enter – manually or by selecting the cells – the formula:

**+’Quarter 1′!B1+’Quarter 2′!B1+’Quarter 3′!B1+’Quarter 4′!B1**

A better approach would be to enter this formula on your ‘Year’ Worksheet:

**=SUM(‘Quarter 1:Quarter 4’!B1)**

or, better still, enter:

**=SUM(‘*’!B1)**

This isn’t much quicker when you’re working with four quarters, but with 12 months or 52 weeks, this would be a real timesaver.

Note that, if you add an extra worksheet before Quarter 1 or after Quarter 4, this won’t be picked up in the formula, but if you slot one in between Quarter 1 and Quarter 4 it will be.

This has saved me countless time.

I previously had 150 sheets of a workbook, split into three sections being added up using VBA and giving three totals sheets. It took about 5 minutes.

As that * is a wildcard it can be used in the format

`=SUM('Car_*'!F3)`

will just add up worksheets starting Car_ which is exactly what I needed to split out the three discreet sections of my project.Can’t thank you enough.

Super tip – very useful and saves lot of time & effort.

A tweak…

Put in a blank worksheet at the start of your required rollup range and a blank worksheet at the end.

Then just sum the sheets in between the two, something like:

=SUM(‘ROLLUP Start:ROLLUP End’!X115)

This allows you to drag sheets in and out of the sum as you need to.

Hey can you explain the tweak of ROLLUP?

I cannot understand its syntax and cannot find the same on the net.

I know this is late, but I believe, he’s just calling his worksheet name as Rollup Start and Rollup End and is keeping them intentionally blank so that you can move whatever in between and it sums it up.

=SUM(‘*’!B1), this is giving me Formula Parse error. How can I get past this?

I am at a loss. I have tried this formula and it does not work for me. When I enter formula “=SUM(‘*’!B2)” to sum up my multiple worksheet cell B2, I get a pop up window when I press enter. The window heading is “update” values. When I enter the formula, instead of the total “3NAME?” is in the cell. Please help…. p_wasilewski@hotmail.com

Did you copy and paste the formula from this page? You’ll get an error if you do that. Try just typing it in.

Thank you for the valuable Information

On this topic..Does anyone know how to sum multiple sheets that have formulas? I have the same chart that has the same vlookup and sum formulas on each tab however when I try to sum all of those sheets- I get an error…thoughts?

Thanks.

Great stuff, thanks!

Regarding Paul W’s problem, It’s important not to copy and paste the text above, but instead physically type it out. The reason is because it’s using nonstandard single quotes (it’s similar to how you can type in a quote in Word vs. Notepad).

Excellent!

THANK YOU!!!! That just saved me a boatload of time!

When I use this formula it works, but it does not include the current worksheet. For example, let’s say I have the same template with a new worksheet for each work week. In the right hand column of EVERY worksheet, I have a “Monthly Total” running column. In that column, I would like to use the formula =SUM(‘*’!B7) and have it add up everything in B7 for every worksheet, INCLUDING THAT CURRENT WORKSHEET. However, whenever I use this formula, Excel converts it to =SUM(‘Worksheet1:Worksheet10′!B7), when for example I am currently working in Worksheet11. It doesn’t include B7 from the current worksheet. I can manually change the formula to be =SUM(Worksheet1:Worksheet11’!B7) and do this and it works, however then literally every week when I add the new worksheet I have to go back and update every single worksheet over again to update this formula to include the most recent worksheet. This is a huge pain. Is there a formula that will add up the values in cell B7 across ALL WORKSHEETS in an Excel file, including any new ones that you add, minus any that you delete? Basically a master formula to include B7 from every sheet in your document.

Thanks for any advice.

Cheers!

You need a blank worksheet before and after the range you want to add up. Rename them START and FINISH respectively. Put the worksheet where you want the total to show in front of the START worksheet (or after the FINISH worksheet). Enter the formula =SUM(‘*’!B7) on that worksheet. Excel will change the formula to =SUM(START:FINISH!B7). When you add your next week’s worksheet, drag its tab to move it between the START and FINISH worksheets and Excel will include it in the total.

SAVED ME A TON OF WORK!!!! THANK YOU!!!

This works great, but I need to go one step further, I need to insert a total sheet after a block of sheets. You can only use the sheetname “Start” and/or “Finish” once, is there a way to insert several start finish sheets?

You can call the START and FINISH worksheets anything you like. Think of them as brackets around what you want to add together, so it’s easiest to give them meaningful names such as QUARTER1 START/FINISH, QUARTER2 START/FINISH, for example.

I like the formula, but is there anyway to combine with Sumif. I have tried many combinations. Problem is Total is not in the same row on every worksheet.

hello this is good but im struggling with having a sum of multiple cells running down vertically in 7 cell intervalls and displaying this information on a master spreadsheet that runs horizontal and without the interval spacing. the idea is to show data for each week on the master and show data each day on the other spread sheet. please help and sorry if im not being very clear

Nice 🙂

Hello, how we can add together functioned cells in multi sheets? suppose we want to calculate the sum of tangents for one similar cell (Here B3) in multi sheets:

Total = SUM(TAN(Sheet1!B3), TAN(Sheet2!B3), ….. TAN(Sheet15!B))

I tried the SUM(TAN(Sheet1:Sheet15!B3) but it didn’t work. may you help me?

Hoping someone can help. I am trying to add 5 years (tabs) of P and L data into a total sheet but it is broken down into different contracts/cost centres, some of which span over 2 or even 3 financial years depending on the life of the contract. So in column A2:A30 for example I have listed the P and L items and from column B1:GD1 I have listed all the different contracts (there are loads). As some of the contracts span over 2 financial years, I effectively want the total costs for each contract at any given time on one spreadsheet instead of having to manually add them each time I want to know the total costs. I know there must be a simple way to do this and know what I want to bring back but am unsure how to write the formula. Any guidance would be greatly appreciated.

eg I have year 1, 2 and 3 on separate tabs and then the total tab I am trying to formulate. In column A (A2:A30) on each tab I have listed PL breakdown of costs of sales egs agency labour, consumables, plant hire, wages, waste etc. In each year there are different contracts eg year 1 A001, A002, A003……..A010 (listed rows B2………..K10) In year 2 there are contracts B001….B012 but also costs for A002 and A005. In year 3 there are contracts C001…..C015 but there are also costs from year 1, say A005 and year 2, say B006 and B008. In the total spreadsheet B1:GD1 I have listed every single contract from the start and want to pull through the total from year 1 to year 3. I think there needs to be some sort of IF formula in the total sheet or not sure if need to do a LOOKUP.

Hi please help to write a sum formula in the same cell exp: there is cell D3 and Cell D4 i would like to have sum value in D4 like d4=d3+d4. Please help me

I have a spreadsheet that copies cell info from 2 additional sheets. I need to filter the columns on all three pages in various different ways but I always need the data in each cell on the main spreadsheet to stay synched with the original cell I synched it to. How do I make the data in the cells on my main page absolute so I can filter (in ascending or descending order) on one page without the originally aligned cells going out of synch? Here is an example of the copied cell: =SUBTOTAL(9,digital!U30)

In the example below, I want to add an additional three rows. By highlighting three rows and then clicking insert, I’m able to add an additional three blank rows into my spreadsheet quickly and easily.