3D Sum Multiple Worksheets

Excel allows us to sum the same cell ranges on the multiple worksheets by using SUM function with 3D reference syntax in the formula. This step by step tutorial will assist all levels of Excel users in summarizing the values in the same cell position on the several worksheets.

Figure 1. 3D Sum Multiple Worksheets

Syntax of the SUM Formula for Multiple Worksheets

=SUM(first_sheet:last_sheet!cell)

The parameters of the SUM function are:

• first_sheet – a first sheet name for summarizing the data
• last_sheet – the last sheet name for summarizing the data
• cell – cell or cell range reference from which we want to sum the values

Setting up Our Data for the 3D SUM Function

Our table in sheet “Total” consists of 2 columns: “Product ID” (column B) and “Quantity” (column C).

Figure 2. Table structure for 3D SUM example

The same table structure is in the same place in all sheets: “Jan”, “Feb”, “Mar” and “Apr”. The idea is to summarize the “Quantity” data per “Product ID” from all monthly sheets. The result will be presented in “Total” sheet in column “Quantity”.

Figure 3. Monthly data that we will use in the 3D SUM example

Summarize the Data from the Multiple Worksheets

We want to summarize the “Quantity” data per “Product ID” from sheets: “Jan”, “Feb”, “Mar” and “Apr”. For this, we will use the SUM function with the 3D reference because the structure and the position of the data are the same in all monthly sheets.

The formula looks like:

=SUM(Jan:Apr!C3)

The first_sheet is “Jan”, while the last_sheet is “Apr”. The cell reference from which we want to summarize the data is in the cell C3.

To apply the SUM function with 3D reference, we need to follow these steps:

• Select cell C3 in the sheet “Total” and click on it
• Insert sign “=” and type function SUM with open bracket “ =SUM( “
• Select the first monthly sheet with the data “Jan”
• Hold the Shift button and select the last monthly sheet “Apr” and cell C3
• Press enter
• Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

Figure 4. Using the SUM function with 3D reference for summarizing the data from multiple sheets

Formula summarizes the values from the first_sheet to the last_sheet in the defined cell range. To include a new sheet in a sum range we just have to place it somewhere between the first and the last sheet from the formula. To use 3D reference in SUM function it’s important to have the same data source structure and the position.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.