In order to use values from the same cell or range but from different worksheets, a 3D reference is used. 3D referencing is useful where multiple worksheets use the same data pattern and layout.
What is the 3D Sum formula?
3D Sum is a part of the 3D Referencing system in Excel, and is very similar to the ordinary Sum function, except it can work across multiple worksheets. You can specify all worksheets or only a select few, and the formula will use data available in the given cell/range from within those worksheets only.
How to use the 3D Sum formula on multiple worksheets?
The 3D Sum formula is a straightforward function that requires you to specify worksheets and data cell/range.
Formula or Syntax
The standard Sum function can accept sheet range, followed by a cell or cell range to produce a 3D Sum/aggregate.
Let us understand the 3D Sum formula better with the help of an example.
1. We will take a look at a sample dataset that contains basic sales figures across multiple product categories for a sports and outdoor equipment store.
Figure 1. Sales table example for 3D Sum function
2. Note that these are the sales statistics for the month of January. We have different worksheets, each containing sales data for subsequent months.
3. Now let’s apply the 3D Sum formula. We’ll go to the last worksheet where we will generate a summary for all 6 months.
Figure 2. Apply 3D Sum formula to sample data
4. Pay attention to the 3D Sum formula we have written in the formula bar. Hit Enter, and you should see the sum total of products in each category from all 6 worksheets.
Figure 3. 3D Sum across multiple worksheets applied
5. You can now go ahead in a similar fashion and apply the formula again for calculating total sale value.
- There should not be any whitespace inside the SUM function brackets. If you put any space between worksheet name and the “!” sign, or the “!” sign and the cell name/range, Excel will prompt you to fix the syntactical error.
- Instead of manually writing sheet and cell names/ranges, you can hold down the Shift key and make an automatic selection.