SUM across different worksheets (aka 3D SUM)

Did you ever make a formula like the one below?  After reading this post, I hope you will never make a formula in this way again.


It is quite common that we hold many worksheets of identical structure in a workbook; and have a summary worksheet which is basically the total of all worksheets in the workbook.  As you see in the example below, the workbook holds daily sales record.  In total, there are 32 worksheets – one Total and 1 to 31 for each day in a month.


The lengthy formula can be replaced by:



Is it simple and easy to construct and read?

Literally, =SUM(‘1:31’!L8) tells Excel to SUM the value in cell L8 of all worksheets from ‘1 to ’31 (both inclusive).  You may refer to “SUM vs. +” for the benefits of using SUM instead of “+”.

You may input the formula directly in the formula bar.  If you want to use mouse to point to the reference you need, follow the steps below:

Suppose you want to sum the value in L8 in all the worksheets, as in the example shown before.

1) In cell L8, type =sum(

ImageNow you are ready to input the first number or reference

 2) Use your mouse to select Sheet “1”


Note the change in the formula bar


3) PRESS the SHIFT Key and select Sheet “31” by mouse.  Again note the change in the formula bar, Excel writes part of the formula for you.


4) The last part is to input the cell reference.  In this case, it’s L8. (you may input in formula bar directly, or select cell L8 with your mouse)


After you create the formula in the right place, you may simply copy it down and across to complete the summary report.


Tips: One of the advantages of using SUM is the flexibility of add new record inbetween. It applies to 3D SUM too.

In screenshot below, a new spreadsheet “1 (Special Event)” of identical structure is placed right after worksheet “1”


In the worksheet “Total”, the SUM formula automatically include the new data.


IMPORTANT: All worksheets MUST be IDENTICAL in structure in order to get the desired result.

This entry was posted in Excel Tips, Formula and tagged . Bookmark the permalink.

9 Responses to SUM across different worksheets (aka 3D SUM)

  1. vikas says:

    Why it is called 3-D?????


  2. Vikas says:

    It is a wonder.
    It was beyond imagination.

    Thank a lot. You open a new path to explore and research.

    Thanks again.


  3. Vikas says:

    Q1: If I write the formula using keyboard (without mouse ) will it work?
    Q2 : why is it called 3 D? Is it three dimensional or any other meaning?

    Thanks & Regards.


  4. Lien says:

    An impressive share! I’ve just forwarded this onto a co-worker who had been doing a little research on this.
    And he in fact bought me lunch due to the fact that I discovered it for him…
    lol. So allow me to reword this…. Thanks for the meal!!
    But yeah, thanks for spending the time to talk about this subject here on your internet site.


  5. Pingback: How do you sum cells in the same position on multiple worksheets?

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s