How to Sum Across Multiple Sheets in Excel

In this tutorial, you will learn how to sum across multiple sheets in Excel.

Use the less well-known 3D referencing capability in Excel if you need to calculate the total across many worksheets. With 3D referencing, you may use formulas like the SUM, AVERAGE, or COUNT to refer to the same cell across many worksheets.

Once you are ready, we can start by using real-life scenarios to help you understand how to sum across multiple sheets in Excel.

Sum Across Multiple Sheets in Excel

Before we begin we will need a group of data to be used to sum across multiple sheets in Excel.

Step 1

First, you need to have a clean and tidy group of data to work with. You can see the sales from quarter 1, 2 and 3 are separated into its own excel tabs.

Step 2

In order to sum across multiple sheets using the 3D referencing, we will insert the following formula =SUM(‘Q1 Sales:Q3 Sales’!B1).

Step 3

Once you are done, the formula will return the sum of the sales across all 3 quarters.

Step 4

Take note! In order for this formula to work, the placement of the data you want to sum across multiple sheets must be in the same cell.