3D sum across all sheets - without VBA!

How can we calculate the sum of cell A1 across all sheets in the workbook, without limitations such as having the sheets in the correct order, or naming sheets in a particular way? It's easy enough to write an UDF that does the trick. But a formula solution has eluded us for years. Finally, it looks like this problem has been solved!

The principles involved are simple, but a whole lot of components are involved - the final solution involves the INDIRECT function, XLM functions, named formulas and array formulas. This article will walk you through the whole reasoning behind the solution, step by step. There is also a sample workbook with both the final solution, and all the intermediate steps.

The Solution

If you just want the answer, here it is:

1. Define a named range - I called it Addr:

=IF(GET.WORKBOOK(1)=GET.DOCUMENT(1),"","'"&GET.WORKBOOK(1)&"'!"&ADDRESS(ROW(),COLUMN()))

Note the use of single quotes before and after GET.WORKBOOK(1).

2. Use the following array formula in any sheet to get the sum of the same cell across all other sheets:

=SUM(IF(ISERROR(N(INDIRECT(Addr))),0,N(INDIRECT(Addr))))

Quick Explanation

The named range returns a list of cell references for all sheets, excluding the calling sheet. It adds single quotes around the name of the workbook and worksheet, in case there are spaces in the workbook / worksheet names. Used in cell A1 in a sheet, it will return an array like " ", "'[book1.xls]Sheet1'!$a$1", "' [book1.xls]Sheet2'!$A$1", etc.

The array formula uses INDIRECT to get the values of those cells. It then uses N() to convert the results of INDIRECT into proper arrays. Next it uses ISERROR to get rid of the error caused by the first, empty cell reference, as well as references to chart sheets. Finally, SUM to sum it all up.

Step by Step

The example file I am using for this has 4 sheets: Sheet1 through Sheet3 that hold the values, and Totals that will hold the sum. I filled Sheet1 with 1s, Sheet2 with 2s and Sheet3 with 3s.

Step 1 - The Basics

The first step towards our goal is a simple 3D formula:

=SUM(Sheet1:Sheet3!A1)

This is nice, and returns 6 as it should, but only works if the sheet order doesn't change. Move Sheet3 to before Sheet1, and the formula will no longer sum all sheets. We need more flexibility than that.

Step 2 - INDIRECT()

Step two: let us make that formula flexible, so that we can easily adjust it if we change our mind about the sheet order. The plan is to use INDIRECT and later replace the hard-coded sheet names in the formula with references to named ranges. So let us try this array formula:

=SUM(INDIRECT("Sheet1:Sheet3!A1"))

The formula looks perfectly OK, but doesn't work - the result is a #REF! error. For some reason, INDIRECT simply refuses to work with 3D ranges.

(Note that all formulas from here on are array formulas, and should be entered using Ctrl+Shift+Enter, not just Enter.)

Undeterred, let us test a modification, which at first looks like it's taking us in the wrong direction. Let us create a list of all the sheets we want to sum, and use that as an input to the INDIRECT function. I named my range with the sheet list "SheetList", and tried this array formula:

=SUM(INDIRECT(SheetList&"!"&"A1"))

This didn't give an error, but Excel seemed to have trouble with adding, since it claimed that 1+2+3=1... As an experiment, move the sheets around a bit, and it will be clear that INDIRECT is only "adding" the first sheet.

Luckily, the Excel Experts E-letter (back issues of which can be found here http://www.j-walk.com/ss/excel/eee/ ) reports that the N() worksheet function can induce INDIRECT to treat the sheet list properly, as an array. The following array formula correctly returns 6:

=SUM(N(INDIRECT(SheetList&"!"&"A1")))

This is much better, since the results no longer depend on the sheet order. However, we still need to maintain a list of sheet names, and the formula is only summing A1 and not all the cells we want.

Step 3 - ADDRESS()

Of these two issues, let us attack the easier one first - let us get rid of the hard-coded cell references.

For this, Excel has an ADDRESS() function that returns the address of a cell based on a row number and a column number. Combine that with the ROW() and COLUMN() functions, and we can get the address of the current cell:

=ADDRESS(ROW(), COLUMN())

This returns $A$1 for A1, etc.

Put that in the INDIRECT formula, and we get this array formula:

=SUM(N(INDIRECT(SheetList&"!"&ADDRESS(ROW(),COLUMN()))))

This can be put in any cell, and will return the sum of that cell across all sheets in the SheetList.

Step 4 - XLM

Now we "just" need to get rid of the hard-coded list of sheets.

For this, Excel's worksheet functions are no longer enough, and we need more powerful tools - namely XLM macros. XLM is Excel's old macro language that predates VBA. For backwards compatibility, XLM functions can still be used in new versions of Excel.

XLM functions are not covered by the Excel help files, but you can download the help files for XLM functions from http://www.microsoft.com/downloads/ - search for macrofun.exe, or for xlmacro.exe. The latter was last spotted here.

We are going to use two functions here: GET.WORKBOOK and GET.DOCUMENT.

• GET.WORKBOOK returns information about the current workbook. GET.WORKBOOK(1) will return a list of all worksheets in the workbook.
• GET.DOCUMENT returns information about the current document, which in the case of worksheet formulas means the current sheet. GET.DOCUMENT(1) returns the sheet name.

The main limitation of XLM functions is that they can only be used in named formulas, not directly in a worksheet. So we can break down our formula into two parts - a named formula to replace SheetList, and an array formula like what we had before.

So let us try named formula, named Addr:

=GET.WORKBOOK(1)&"!"&ADDRESS(ROW(),COLUMN())

Try entering it in a worksheet range (I entered it in cells A8 to D8) and you should get results similar to this:

The problem with this is that our Totals sheet is included in the results. If we used this in a formula, the calculation would be circular. We need to exclude the current sheet from the array.

This is where the GET.DOCUMENT function comes in. We modify our formula so that the current sheet is excluded, and replaced with a blank. For the sake of clarity I've broken down the formula in 3 pieces here, but you would of course enter it as a single piece:

=IF(GET.WORKBOOK(1)=GET.DOCUMENT(1),
"",
GET.WORKBOOK(1)&"!"&ADDRESS(ROW(),COLUMN()))

Used in a worksheet formula, the named formula should now yield something like this:

Now, if we tried to use any of these range references in an INDIRECT function, we would still get an error, because of the space in the workbook name. To handle that, we need to put single quotes before and after the workbook / worksheet names:

=IF(GET.WORKBOOK(1)=GET.DOCUMENT(1),"","'"&GET.WORKBOOK(1)&"'!"&ADDRESS(ROW(),COLUMN()))

This should return:

Step 5 - Putting It All Together

Almost there now!

We only need to get rid of that error value, and sum the other values. Importantly, we also need to take into account the possibility that there are chart sheets in the workbook, and exclude those.

We will use ISERROR to get rid of any invalid addresses, and INDIRECT() and N() to sum the valid values, as before. For the sake of clarity, I've broken down the formula over 4 lines here:

=SUM(
IF(ISERROR(N(INDIRECT(Addr))),
0,
N(INDIRECT(Addr))))

Bingo! The formula now sums the values in the current cell across all sheets, excluding the calling sheet.

If you want to make the worksheet easier to read at a glance, you can create a second named formula - name it ThreeDSum for example - that refers to the SUM() formula we've just created. In your worksheet, you would then simply enter "=ThreeDSum" as the formula.

Notes and Comments

Since this technique utilises XLM macros, Excel 2003 users will get a warning message when opening a file that uses this. The technique will not work if XLM macros are disabled in the file.

The XLM macro language is localized. If you have non-English Excel, you will need to translate not only the worksheet functions, but the XLM formulas as well. Luckily, existing XLM formulas get converted when opened in another version of Excel, so you can use the sample workbook here to find out what you local equivalent of "GET.WORKBOOK" might be. Alternatively, get your local version of xlmacro.exe at http://www.microsoft.com ( download it here).

I hope you will find this formula useful, and that my explanation has shed some light on how it works. You can also download a sample workbook that shows the formula in action.

Special thanks to member Insomniac at http://www.xtremevbtalk.com for inspiration, corrections, and help with testing this.

Site menu:

Latest News

29/12/2021 - The site has been moved to a new server to be maintained for history's sake! It's all pretty old now...

28/05/2006 - MS Office 2007 Beta 2 has now been released.

25/05/2006 - The newly formed Professional Office Developers Association is now open to membership!

22/03/2006 - How to get windows to do a text search on PHP files (or any other filetype) that it ignores by default!

09/03/2006 - New website design is live!

12/02/2006 - First ASP.Net article is online now DropDownListBoundColumn for ASP.Net Datagrid

01/09/2005 - Learn how to create a fixed width text file from an excel worksheet.