SUM across different worksheets (aka 3D SUM) - KING OF EXCEL

Friday, January 10, 2020

SUM across different worksheets (aka 3D SUM)

Image
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.
Image
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.
Image
The lengthy formula can be replaced by:
=SUM(‘1:31’!L8)
Image
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”
Image
Note the change in the formula bar
Image
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.
Image
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)
Image
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”
Image
In the worksheet “Total”, the SUM formula automatically include the new data.
Image

IMPORTANT: All worksheets MUST be IDENTICAL in structure in order to get the desired result.
#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

Popular Posts