Sum All Sheets
July 17, 2017 - by Bill Jelen

You need to total numbers on Jan Feb Mar ... Dec
So far, you have a workbook with 12 worksheets, one 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.
The formula is =SUM(January:December!B4)
.


Caution
I make sure to never put spaces in my worksheet names. If you do use spaces, the formula would have to include apostrophes around the sheet names:
=SUM('Jan 2018:Mar 2018'!B4)
Thanks to Othneil Denis for suggesting 3D formula and Olga Kryuchkova for suggesting Group mode.
Watch Video
- You need to total numbers on Jan Feb Mar … Dec
- You could =Jan+Feb+Mar…
- But there is a faster way called a Spearing formula
- Also known as a 3D reference
- Start by typing =SUM(
- Click on first sheet
- Shift-click on last sheet
- Click on the cell
- Type ) and press Enter
- Anything between Jan through Dec is included
- Don't add new sheets with Grocery List 7 gallons of milk
- Don't randomly move August outside
- Mack Wilk trick using Start> and
- Bizarre Bob Umlas wildcard trick
=SUM('J*'!B4)
- Thanks to Othneil Denis for suggesting 3D Formula Reference
Download File
Download the sample file here: Podcast1984.xlsx
Title Photo: WikiImages / pixabay