MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sum All Sheets


July 17, 2017 - by Bill Jelen

Sum All Sheets

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

SUM Formula
SUM Formula
Formula Copied in Data Set
Formula Copied in Data Set

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


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.