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 Unsplash.com

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.

Caution

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

Tip

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 Unsplash.com

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