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(
Now 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.
Why it is called 3-D?????
It is a wonder.
It was beyond imagination.
Thank a lot. You open a new path to explore and research.
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.
Yes you may input the formula directly. Pay attention to the ‘ and ! Though.
3D means three dimensional.
I am amazed again.
How did you reply so quickly? !
Coz it happened I just finished breakfast 😁 and have a few minutes before work.
Btw you may be also interested in the following post
Take a look when you have time.
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.
Thanks for your kind words. You are welcome!
Pingback: How do you sum cells in the same position on multiple worksheets?