MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Create a SUM That Spears Through All Worksheets


February 27, 2019 - 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


Bill Jelen is the author / co-author of
Microsoft Excel 2019 Pivot Table Data Crunching

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.