MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sum All Sheets Faster

May 04, 2017 - by Bill Jelen

Sum All Sheets Faster

A faster way to build an Excel 3D formula, also known as a spearing formula. I usually type =SUM(Jan:Dec!H10), but you can do this using the mouse.

Watch Video

  • Sum a rectangular range on all worksheets
  • =SUM(
  • Click on Jan sheet
  • point to the range
  • Shift + Click on Dec sheet
  • press Enter
  • Or, type =SUM(Jan:Dec!B4)
  • Bonus trick: Adding Total Row & Column in one click of AutoSum

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode 2090
  • the 3d reference with two sets of colons
  • hey welcome back to the mr. cell net
  • cast I'm bill jealous question actually
  • came up in my seminar in Bloomington
  • Indiana and I usually talk about these
  • 3d references where we have all of these
  • different month names and then show how
  • to build a Spearing formula that will
  • add up cell b4 on each of the sheets
  • from January through December but what
  • came up today was no we just want one
  • form that's going to sum the whole thing
  • and yeah that is possible equal sum and
  • I want to come back here and click on
  • January choose all of the cells let's
  • get this out of the way choose all the
  • cells I want to sum from the January
  • sheet and then shift-click on to summer
  • and watch this formula right now right
  • now it's pointing at one rectangular
  • range on one sheet when i shift-click on
  • December I rewrites it into a
  • rectangular range on all of this sheets
  • from January through December I mean
  • don't you have to click the closing
  • parenthesis
  • just press ENTER and it goes and grabs
  • all of those numbers all the way across
  • cool cool formula like that with 2 : 2 :
  • in the sheet name and a colon in the
  • rectangular range we're pulling back now
  • what I usually show is just this little
  • formula here where we have to do equal
  • sum of January : December exclamation
  • point B for like that regular 3d
  • reference but it also works 3d reference
  • pointing to an entire range like that
  • hey let's just do one more bonus trick
  • here since this trick was so short if
  • you have to add totals across the bottom
  • and down the left down our hand side
  • most people come here hit the autosum
  • copy across and then here hit the
  • autosum and copy across check out this
  • awesome awesome way I just choose all
  • the numbers plus one extra column plus
  • one extra row then hit the auto sum and
  • adds the totals all the way around the
  • outside why did the number change
  • because these numbers are still ran
  • between but a cool couple of tricks
  • 3d references absolutely there in the
  • book PowerShell MrExcel the 2017 2017
  • edition click that I on the top right
  • hand corner to read more about the book
  • ok episode wrap up we're going to set my
  • rectangular range on all worksheets
  • start off by typing equals sum click on
  • the January sheet highlight the range
  • with the mouse and then shift-click on
  • the December sheet press Enter don't
  • even use a closing parenthesis or if
  • you're looking for just a single cell
  • equals some January : December
  • exclamation point and the cell that we
  • want a little bonus trick there at the
  • end adding the tilde row and column in
  • one click of auto sum well thanks for
  • stopping by we'll see you next time for
  • another net cast from MrExcel

Download File

Download the sample file here: Podcast2090.xlsm

Title Photo: sardenacarlo / 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.