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**

`=SUM(Sheet1:Sheet6!A1)`

**Explanation**

The standard Sum function can accept sheet range, followed by a cell or cell range to produce a 3D Sum/aggregate.

**Example**

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.

**Notes**

- 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**.

## Leave a Comment