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
Microsoft Excel 2019 Inside Out

Dive into Microsoft Excel 2019–and really put your spreadsheet expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workarounds–all you need to make the most of Excel’s most powerful tools for analyzing data and making better decisions.