Excel 2020: Create a SUM That Spears Through All Worksheets
February 06, 2020 - by Bill Jelen
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
Copy the formula to all cells and you will have a summary of the other 12 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
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.
Title Photo: Shuenz Hsu at Unsplash.com
This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.