The SUM function can perform 3D sums. The method is useful in certain circumstances and is both efficient and flexible. See the example file link at the bottom of this blog post.

You have twelve sheets, one for each month of the year. They are named Jul, Aug and Sep etc. You also have a Report sheet – the very first sheet in the file. Now these 12 monthly sheets are identical in all ways except that the values in them obviously relate to the month involved.

In the Report sheet I want to add up the corresponding cells in all the month sheets. Most users would create a formula that starts like =Jul!B10+Aug!B10+Sep!B10.Very time consuming. There is a better way.

I’ll introduce two more sheets which I’ll call Start and End. They are both blank sheets. Start is the second sheet tab in the file (to the right of the Report sheet and to the left of the Jul sheet). End is the last sheet tab in the file. Because all the month sheets are identical (this is very, very important) we can add the full year figures “through” the sheets. This is the 3D part.

Now the reason I’ve added two sheets (Start and End) is to make the 3D formula easier to create and more flexible. The following formula in the Report sheet adds up all the B10 cells in all the months.

=SUM(Start:End!B10)

You could type the formula to create it, or use the AutoSum. To use the AutoSum simply click the AutoSum and use the mouse to point to cell B10 in the Start sheet then hold the Shift key and click the End sheet and then release the Shift key and press Enter.

Now the flexibility part of this formula comes in the ability to move sheets around. If I move the Jun sheet to the right of the End sheet or the left of the Start sheet, it will NOT be included in the Report SUM calculation. Also, if I added another sheet called Adjustments (identically laid out to the month sheets) I could move that sheet to the left of the End sheet for the adjustment figures to be included in the Report sheet SUM. Whatever sheets are between the Start sheet and the End sheet will be included in the SUM.

Note: because I added two blank sheets Start and End I can easily move the month sheets around. If I hadn’t added those sheets I would have a formula like =SUM(Jul:Jun!B10), which is not as flexible for the Jul and Jun sheets.

I’ve used this technique to demonstrate a month-based report, but you could just as easily do the same for departments, regions or states.

Note: only the top of the sheets need to be identical. The rows below can be different in each sheet as long as the top section is identical.

Join me for the next blog which looks at an alternative to the SUM function. This alternative function has a couple of advantages over the SUM function which I’ll share with you.

Feel free to make comments and offer any feedback or suggestions.

3D SUM Example