MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2020: Create a SUM That Spears Through All Worksheets

February 06, 2020 - by Bill Jelen

Create a SUM That Spears Through All Worksheets. Photo Credit: Shuenz Hsu at

You have a workbook with 12 worksheets, 1 for each month. All of the worksheets have the same number of rows and columns. You want a summary worksheet in order to total January through December.

To create it, use the formula =SUM(January:December!B4).

After sheets for January through December, a new Summary worksheet is shown. The formula =SUM(January:December!B4) is in B4.

Copy the formula to all cells and you will have a summary of the other 12 worksheets.

Copy the formula throughout the worksheet and you will have a report adding up the similar cell on the 12 monthly worksheets.


I make sure to never put spaces in my worksheet names. If you do use spaces, the formula would have to include apostrophes, like this: =SUM('Jan 2018:Mar 2018'!B4).


If you use 3D spearing formulas frequently, insert two new sheets, one called First and one called Last. Drag the sheet names so they create a sandwich with the desired sheets in the middle.Then, the formula is always =SUM(First:Last!B4).

Here is an easy way to build a 3D spearing formula without having to type the reference: On the summary sheet in cell B4, type =SUM(. Using the mouse, click on the January worksheet tab. Using the mouse, Shift+click on the December worksheet tab. Using the mouse, click on cell B4 on the December worksheet. Type the closing parenthesis and press Enter.

Thanks to Othneil Denis for the 3D formula tip.

Title Photo: Shuenz Hsu at

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.

Bill Jelen is the author / co-author of
Excel Insights – A Microsoft MVP Guide to the Best Parts of Excel

There are fewer than 100 Excel MVPs worldwide. 24 of them have contributed to this book. Written, edited, reviewed and printed by Excel MVPs, this is practical Excel passion undiluted, with each MVP highlighting some of their favorite topics.