MrExcel.com - Your One Stop for Excel Tips & Solutions

Sum All Sheets

July 17, 2017 - by

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